Formula for next unique value

[ninja0059]

I am trying to create a formula that would referance a column of numbers and return the next unique number in the list. Example:

Column A
1
1
1
2
2
3
3
3

In column B I would like a formula in each row that would return

Column B
1
2
3

Any helo would be appreciated.

[ChemistB]

The easiest way to do that would be with Advanced Filtering, Unique references only. If you want to use formulas to create a unique list

Please Login or Register  to view this content.

which when dragged down, will copy each new/unique number as it's encountered. Do either of these options work for you?

ChemistB

[ChemistB]

For that second method, to bring the values together in Column C, you'd use this formula copied down

Please Login or Register  to view this content.

where row 40 is the last row of your data. Adjust the row if you aren't starting in Row 1.

ChemistB

[ninja0059]

ChemistB,

It seems that the second formula isn't working for me. Is the =if(=if.....correct?

[ChemistB]

Oops, no

Please Login or Register  to view this content.

ChemistB

[ninja0059]

Thank you for the assistance. I am still having trouble for Column C where the return values should be a clean list (no blank cells) of the unique values in column B....

Help?

[ChemistB]

Here's an example file. See if this helps.

ChemistB

[ninja0059]

ChemistB,

Thanks for your help a while back. I need to revist this question. Can I use this same formula to retrieve Unique TEXT date insteatd of NUMERIC as I previously was requesting. It works beautifully for NUMERIC, but I can't get it to work for TEXT (names for example)..

[Domenic]

Assuming that A2:A10 contains the data, try...

B2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(C$2:C2))),"")

Hope this helps!

[ninja0059]

Works awesome.....1 last thing...Is there anyway to build into the calc an ascending sort of the data. Essentially, i am trying to use this list to build a drop-down menu of the unique values and would love to have it in order....
Thanks so much

[Domenic]

Try...

B2:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX($A$2:$A$10,MATCH(SMALL(IF(ISNA(MATCH($A$2:$A$10,$C$1:C1,0)),MMULT(($A$2:$A$10>TRANSPOSE($A$2:$A$10))+0,ROW($A$2:$A$10)^0)),1),MMULT(($A$2:$A$10>TRANSPOSE($A$2:$A$10))+0,ROW($A$2:$A$10)^0),0)),"")

Note that MMULT will return #VALUE! if the output of cells exceeds 5,460. If this is the case, download and install the free add-in Morefunc, and use MMULT.EXT instead.

Hope this helps!

[ninja0059]

Thanks. I keep getting a #NAME error in when doing the formula. I have attached an example....help...

[NBVC]

The formula you copied from Domenic's post has a space within the last ROW() function.. you need to eliminate that space after copying it over and then confirm with CSE keys.

[ninja0059]

Worked like a charm.

Thank you so much!!!

[NBVC]

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save