Establish identical Values within an index range

Hi,
I would like a formula to establish identical values within an index range.
Please see attached
Many thanks
Ian
identical-values-within-range.xlsx
Ian BellretiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this...

In C2
=IF(COUNTIF($A$2:$A$15,A2)=COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2),0,1)

Open in new window

and copy it down.
Saqib Husain, SyedEngineerCommented:
Another formula

=--(SUMPRODUCT(--(A2:A15=A2))<>SUMPRODUCT((A2:A15=A2)*(B2:B15=B2)))
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To counter the Saqib's formula, here is the more elegant formula... Lol
=--(COUNTIF($A$2:$A$15,A2)<>COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2))

Open in new window

and copy it down. :)
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Ian BellretiredAuthor Commented:
They all take a long time to calculate
Saqib Husain, SyedEngineerCommented:
What is the maximum number of entries in any one index set?
Ian BellretiredAuthor Commented:
40
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
And how many rows of data in there?
After copying the formula down the rows, you can copy the formula range and paste it back as values so that the formulas don't recalculate every time.
Or you can use VBA to place the formula.
In the attached, place the data from your original file in the column A and B starting from Row2 and click the button called "Place Formula" and see if it works faster than manually placing the formula.
identical-values-within-range.xlsm
Saqib Husain, SyedEngineerCommented:
Try this formula

=--(COUNTIF(A2:A51,A2)<>COUNTIFS(A2:A51,A2,B2:B51,B2))
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Saqib
The formula you posted is the same I posted here. What I am missing here?
Saqib Husain, SyedEngineerCommented:
Subodh, I have removed the absolute marks $

But that formula would fail in some scenarios. So a new formula.

=--(COUNTIF(OFFSET(A2,MAX(-50,-ROW()+2),0,100),A2)<>COUNTIFS(OFFSET(A2,MAX(-50,-ROW()+2),0,100),A2,OFFSET(B2,MAX(-50,-ROW()+2),0,100),B2))
Ian BellretiredAuthor Commented:
Here is a formula using the indirect condition.
In fact the rows and columns are correct so a matter of inserting your new formula.
DC and DD are the F & G columns I referred to in my example.
JR being the Index column

=IF(JR14="","",IF(JR14=JR13,CS13,SUM(INDEX((INDIRECT("$JR"&DC14&":$JR"&DD14)=JR14)*(INDIRECT("$A"&DC14&":$A"&DD14)),0))))
Saqib Husain, SyedEngineerCommented:
I think this would be even more quicker

=IF(A2=A1,G1,--(COUNTIF(A2:A51,A2)<>COUNTIFS(A2:A51,A2,B2:B51,B2)))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Applying a bit of logic, if all values in an index range are the same they have to be the same as the Average for the index range.

Therefore, you can use:
=IF(B2=SUMIFS(B:B,A:A,A2)/COUNTIFS(A:A,A2),0,1)

For later versions of Excel:
=IF(B2=AVERAGEIFS(B:B,A:A,A2),0,1)
Rob HensonFinance AnalystCommented:
However, if one of the values in the range is equal to the average then it will result in 0 on that line. So to allow for that:

=IF(COUNTIFS(A$1:A1,A2,C$1:C1,1),1,IF(B2=SUMIFS(B:B,A:A,A2)/COUNTIFS(A:A,A2),0,1))

One scenario where it gives the wrong result is if the first value for the Index range is equal to the average. To overcome that, I would suggest sorting the values in each range in ascending order. On the same logic, if the values are not equal to the average there has to be some that are less than the average and some that are higher. Sorting in ascending order will ensure that the first value is less than the average; if the first/lowest value is equal to the average then all of them will be.
Ian BellretiredAuthor Commented:
Thank you guys. I'd be lost without you :)
Ian BellretiredAuthor Commented:
Hi guys,
I was trying out Rob's simple solution and worked well and calculated in less than a couple of minutes, which is fast
for this 160,000 row spreadsheet

I will work with this one for now but will also run the other formulas and will claim them all as a solution if that is permissible.
Thank You Rob, Saqib and Neeraj
Ian
Rob HensonFinance AnalystCommented:
Thanks Ian, glad it worked well.
Saqib Husain, SyedEngineerCommented:
Are you sure that is is Rob's solution which is fastest?

Besides, you accepted mine.
Ian BellretiredAuthor Commented:
There's not a lot of difference in speed between some of the formulas.
Saqib Husain, SyedEngineerCommented:
That can't be right. I repeated the given data for more than 160,000 rows and the formula gave the results in only a second or two.
Ian BellretiredAuthor Commented:
Which one of your formulas did you use ?
Saqib Husain, SyedEngineerCommented:
The accepted one
Ian BellretiredAuthor Commented:
I will have to check this later as I need to go out.
Ian BellretiredAuthor Commented:
Hi Saqib,
Seems I selected wrong solution.
I need the formula to return either 1 or 0
Saqib Husain, SyedEngineerCommented:
I am leaving for home. I am going to send a file showing comparisons. You can then see the difference.
Ian BellretiredAuthor Commented:
Thanks Saqib very kind of you..
Ian BellretiredAuthor Commented:
Saqib,
I have found an alternative method to arrive at the conclusion without the use of
this formula. I'm understanding more and more about this monster of a spreadsheet
Apologies for inconvenience.
Ian
Saqib Husain, SyedEngineerCommented:
Here is the file. If you see the formulas in the first few columns they take ages to compute. But if you look at the formulas in the last two columns then you would see that the calculations are very much instantaneous.
identical-values-within-range.xlsx
Ian BellretiredAuthor Commented:
okay thanks Saqib I'll check it out.
Ian BellretiredAuthor Commented:
I finally got onto your formula having been away from here yesterday.
I tried your end col formula and it certainly was fast as only took a few
seconds to calculate. I'm off for a long walk and when I get back will
check out your other formula.
Thanks again Saqib your effort was certainly worthwhile from my point of view
Ian
Ian BellretiredAuthor Commented:
Just back from my usual 2 hour Friday walk with my neighbour and tested your formula in G column.
Both work perfectly and came up with identical results and both calculated at a fast speed of a few seconds for 170k rows.
Thanks a million Saqib.
Ian
Ian BellretiredAuthor Commented:
A big thank you to all you guys who submitted solutions. They worked well, some calculated
quicker than others.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.