How to Combine (Concatenate) Data from Multiple Rows into One Cell

Hi all,

My problem is that in my excel worksheet of several thousand entries the item (eg AA number) is included in more than one row, with different attributes listed in the different rows. Aside from the title column, the content in the rows is not duplicated ie if value 1 has an entry for "LOC2/12345/2015/11223" in one row, it does not in the second row, which I hope will make the job of merging the two rows easier.

Appreciate your experience sharing

Thanks

Is the test on Duplicate AA Numbers? Can you add more data to your example to clarify what you want.

Hi,

Yes, the task is for duplicate AA number with difference attributes in the finding.

I have update for more info to better view

VBA version: requires sort on AANUMber

Please Login or Register  to view this content.

Any method beside VBA...
I'm not very good with VBA actually

Hi all,
I think my issue is from vlookup + Concatenate.

Attached together workbook for easy understanding. Hope you could share your knowledge together

Thanks

Here an example of a non-VBA approach which I am not recommending. It requires using "helper" columns to extract the duplicate data and then concatenate the output.

One issue is the number of duplicates vary so any solution would have allow for a maximum.

The formula used to extract the data is..

For column B data

=IFERROR(INDEX(Amended1!$B$2:$C$30,SMALL(IF((Amended1!$A$2:$A$30=$B2)*(Amended1!$D$2:$D$30="Duplicates"),ROW($A$2:$A$30)-ROW($A$2)+1,""),COLUMNS($A$2:A2)),1),"")

For column C data

=IFERROR(INDEX(Amended1!$B$2:$C$30,SMALL(IF((Amended1!$A$2:$A$30=$B2)*(Amended1!$D$2:$D$30="Duplicates"),ROW($A$2:$A$30)-ROW($A$2)+1,""),COLUMNS($A$2:A2)),2),"")



Both entered with Ctrl+Shift+Enter

VBA is a much better solution.