Count The Order Of Occurrence Of Duplicates In Excel/formula to return only unique value

[bjnockle]

Looking for non-array formula to return unique value in Excel. COUNTIF($A$2:$A2,A2) works fine but it is crashing the excel workbook due to the dataset volume (over 200,000 rows). If there is a non-array formula that uses less computing memory, will appreciate it.

See attached sample file.

Thanks

[BMV]

This data for MsQuery, PowerQuery, PivotTable or VBA but not for formula . Even Advanced filter can be used.

[bjnockle]

BMV: goal is to use a formula. If you are able to, please go ahead.

Thanks

[FlameRetired]

Try in cell B2 and fill down.

Please Login or Register  to view this content.

[FlameRetired]

Or insert a helper column using the 'Fill Series' feature. I inserted a new column A in the attached. It cuts way down on calculation overhead. Also to avoid unnecessary overhead of IFERROR array enter this single helper cell in F1 to count the number of unique values.

Please Login or Register  to view this content.

Enter this formula in E2 and fill down.

Please Login or Register  to view this content.

[hrlngrv]

Your sample workbook may be too simplistic. Presumably you have a single column range spanning over 200,000 rows, but your example shows column A sorted in ascending order. If that's the case, a 2 cell/result approach would be most efficient. Data assumed in Other!$A$3:$A$205000, first distinct value in active worksheet cell AA5.

AA5: =Other!$A$3

AB5: =COUNTIF(Other!$A$3:$A$205000,AA5)+1

AA6: =INDEX(Other!$A$3:$A$205000,AA5)

AA6: =COUNTIF(INDEX(Other!$A$3:$A$205000,AB5):Other!$A$205000,AA6)+AB5

Select AA6:AB6 and fill down as far as needed. Note that the column AB formulas perform COUNTIF on successively smaller ranges in lower rows.


If your data isn't sorted, it should be with that many records. Also, you could really boost performance by adding supporting formulas in the Other worksheet.

X3: =A3<>A4

Fill X3 down into X4:X205000.

Y3: =MATCH(TRUE,X3:X$205000,0)

Y4: =IF(X3,MATCH(TRUE,X4:X$205000,0))

Fill Y4 down into Y5:Y205000.

Z2: =COUNT(Z3:Z205000)

Z3: =Y3

Z4: =Z3+INDEX(Y$3:Y$205000,Z3+1)

Fill Z4 down until it produces #REF! errors. The col Z values are row indices of the last/bottommost instances of each distinct value in Other!A3:A205000.

Generate a list of distinct values from Other!A3:A205000 as

AA3: =IF(ROWS(AA$3:AA3)<=Other!$Z$2,INDEX(Other!$A$3:$A$205000,INDEX(Other!$Z$3:$Z$205000,ROWS(AA$3:AA3))),#NULL!)

Fill AA3 down until it produces #NULL! errors. FWIW, I believe this is the most efficient way to index distinct values until Excel provides the UNIQUE function to all. The first approach above is O(N^2) while the second approach is O(N log(N)) because MATCH is more efficient than COUNTIF. COUNTIF processes it's entire 1st argument range, while MATCH stops once it finds a match.

[BMV]

BMV: goal is to use a formula. If you are able to, please go ahead.

Thanks

Any formulas will work slow for 200k rows. I have 2 versions of Excel and x32 can't fill down any of offered and created by my self at all
=IF(MATCH(A2;$A$1:A2;)=ROW();ROW())
and
=IFERROR(INDEX(A:A;SMALL($C$2:$C$200000;ROW(D1)));"")

[bjnockle]

FlameRetired: Is there a way to use helper column and bypass "Countif" to return the first occurrence of a value in a column? This

Please Login or Register  to view this content.

formula is crashing the excel workbook because it has over 290,000 rows. If there is helper column and the formula is not forcing Excel to execute the "calculating operation, which slows down the sheet considerably and/or crashes it. See example workbook from initial post.

Thanks

[bjnockle]

BMV: Is there a way to use helper column and bypass "=IF(MATCH(A2;$A$1:A2=ROW();ROW())" to return the first occurrence of a value in a column? This

Please Login or Register  to view this content.

formula is crashing the excel workbook because it has over 290,000 rows. If there is helper column and the formula is not forcing Excel to execute the "calculating operation, which slows down the sheet considerably and/or crashes it. See example workbook from initial post.

Thanks

[BMV]

bjnockle,I think you should return to #2. Why not PivotTable or MsQuery. Last one need path correction and it can be done by small macro or manually. And look at the #6. Sorted data can be calculated very easy.

[bjnockle]

BMV: And look at the #6. Could not follow #6. I think there is a way to use multiple helper columns to achieve the same result.

Thanks

[kvsrinivasamurthy]

Is the data is sorted in original file as shown in sample file. if it is so in c2 then copy down

=IF(A2=A1,C1+1,1)