Is there a way to consolidate names in a column without using PT or the Consolidate fctn?

I'm looking for a formula that given Column A filled with names (a lot of them repeating) will return Column B as a list of names with no duplicates. For example:

Column A

John
Juan
Juan
Jose
Jose
Jose
Joseph
Myra
Myra
Lina

Will be:

John
Juan
Jose
Joseph
Myra
Lina



I don't want to use Macro, PT, or the Consolidate function. I already know how to do it with those 3 options. I'm looking for a cell formula. Well, I don't know how to do it in Macro but that will be a future challenge. Thank you

This method uses a helper column (e.g. column B, which could be hidden to maintain the look of your sheet).

Assuming your names start in cell A2, put this formula in B2:

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")

Copy this down to the bottom of your data. Then put this formula in cell C2:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

and copy this down until you start to get blanks.

Hope this helps.

Pete

Originally Posted by Pete_UK

This method uses a helper column (e.g. column B, which could be hidden to maintain the look of your sheet).

Assuming your names start in cell A2, put this formula in B2:

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")

Copy this down to the bottom of your data. Then put this formula in cell C2:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),B:B,0)),"")

and copy this down until you start to get blanks.

Hope this helps.

Pete

Thank you! This works perfectly!

Glad to hear it.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete