Combine Text Fields within Column for Duplicate Entries in Adjacent Column

Originally Posted by ANKRULL

Hello,

Relative newb here. I already apologize for not explaining this correctly, but I'll do my best! I need to combine text fields from within a column for duplicate entries in an adjacent column. BELOW, the first two columns show what I have and the third column shows my desired product.

Product - Location - ALL Locations
apple - shelf 1 - shelf 1
banana - shelf 2 - shelf 2
yogurt - shelf 3 - shelf 3 and shelf 4
yogurt - shelf 4 - shelf 3 and shelf 4

In other words, when a product is on more than one shelf, I need to list each shelf in a single cell.

I can't use a PIVOT table for my application because I will be linking this to an ACCESS database later via the common field (product), and want to show the combined field (ALL Locations) for each product.

I greatly appreciate any help!

Thanks,

Alex

Would you be happy with VBA function?

Hi and welcome to the forum

How many total possible locations could there be?

Press Alt+F11
Enter this code as modul:

Please Login or Register  to view this content.

Then use this formula in C2 (confirm Control+Shift+Snter)
=IF(A2="","",SUBSTITUTE(AConcat(IF($A$2:$A$10=A2,","&$B$2:$B$10,"")),",","",1)) and drag down

Shelf.xls