Excel Magic Trick # 159: VLOOKUP Duplicates 1st Column

Solve Duplicate VLOOKUP function problem with COUNTIF formula that creates a column of unique values for the first column in the lookup table.
COUNTIF function & ampersand & concatenation to create column of unique values in first column of lookup table so that VLOOKUP function can deal with duplicates.
Also see: Excel Magic Trick #148: Dynamic List from Table #3

Category:

Science & Technology

Tags:

• Excel
• Duplicate
• VLOOKUP
• function
• COUNTIF
• formula
• lookup
• table
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[bridgetcollins]

Hi

If I have a list of items in one cell (comma seperated) which correspond to the first column in another data set, can I use a look up to return multiple values, all of which would be in column 2 of my data set?

[ExcelIsFun]

I do not know. Try psoting to:

mrexcel [dot] com/forum

[missy3840]

What if I need a chart that has the duplicates removed from column 1, and the column next to it concatenates the cells (separated with commas) with the same name. For example I would need in the first cell Name 1 and in the cell next to it would have 783 Ave, 966 Street, 327 Blvd. And under that would be Name 2 with the matching addresses separated by commas.  Is there any way to do this?

[ExcelIsFun]

I do not know how to do that. The best place to ask Excel questions is:

mrexcel[dot]com/forum

[kaetos03]

Thank you señor!

[ExcelIsFun]

You are welcome!

[ExcelIsFun]

You are welcome!

[udanial]

thank you sir for wonderful videos.

[ExcelIsFun]

EXCELlent!!!!

[saladproblems]

You saved me about an hour of my life!