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.

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

I would like a formula to establish identical values within an index range.

Please see attached

Many thanks

Ian

identical-values-within-range.xlsx

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

```
=--(COUNTIF($A$2:$A$15,A2)<>COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2))
```

and copy it down. :)
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

The formula you posted is the same I posted here. What I am missing here?

The formula you posted is the same I posted here. What I am missing here?

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

=--(COUNTIF(OFFSET(A2,MAX(

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=JR1

=IF(A2=A1,G1,--(COUNTIF(A2

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 trialTherefore, you can use:

=IF(B2=SUMIFS(B:B,A:A,A2)/

For later versions of Excel:

=IF(B2=AVERAGEIFS(B:B,A:A,

=IF(COUNTIFS(A$1:A1,A2,C$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.

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

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

identical-values-within-range.xlsx

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

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

quicker than others.

Spreadsheets

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

In C2

Open in new window

and copy it down.