How to Concatenate like values in a column

[Joe Walsh]

Hi All,
I have a large column of ID codes can would like to concatenate the id codes that match each other. Maybe there is a formula that would be able to do this?? My goal is to have a the same column with id codes but all of the like codes in the same cell.

Ex.

Column: A
1 H3367B12K
2 QK271P3A
3 H3367B12K
4 Z4F71X20
5 Z4F71X20
6 QK271P3A
7 T8IQ75V3
8 H3367B12K


Result:

Column: A
1 H3367B12K, H3367B12K, H3367B12K
2 QK271P3A, QK271P3A
3 Z4F71X20, Z4F71X20
4 T8IQ75V3

Thanks for the Help!

Array Formula Maybe?

[Pauleyb]

Wow, no responses.

I came up with a few partial solutions. My attempts to use INDEX to filter out repeats was getting too complex.

Easiest solution
In column B: =LEFT(REPT(A1&", ",COUNTIF($A$1:$A$8,A1)),COUNTIF($A$1:$A$8,A1)*(LEN(A1)+2)-2)
Then use Remove Duplicates

[Joe Walsh]

Thanks Pauley for the help,

The Forumula works perfectly! One more thing about the data, Column A is the first of 10 Columns and ideally I would like to combine the corresponding lines of data with the concatenated id codes.

I changed the formula just a little bit to fit my worksheet:
=LEFT(REPT(Notes!$E2&CHAR(10),COUNTIF(Notes!$E$2:$E$10,Notes!$E2)),COUNTIF(Notes!$E$2:$E$10,Notes!$E2)*(LEN(Notes!$E2)+2)-2)

Here is the example:


Column: A
1 H3367B12K
2 QK271P3A
3 H3367B12K
4 Z4F71X20
5 Z4F71X20
6 QK271P3A
7 T8IQ75V3
8 H3367B12K

Column: B
1 11:21:23
2 12:42:13
3 13:55:45
4 13:57:56
5 14:11:29
6 15:14:31
7 16:28:46
8 16:45:21




Result:

Column: A
1 H3367B12K
H3367B12K
H3367B12K
2 QK271P3A
QK271P3A
3 Z4F71X20
Z4F71X20
4 T8IQ75V3

Column: B
1 11:21:23
13:55:45
16:45:21
2 12:42:13
15:14:31
3 13:57:56
14:11:29
4 16:28:46

Thanks!

[Pauleyb]

Hi Joe,
thanks for the rep bump.

I'm not sure if your last post is a request for help to also include column B, or if you got it to work...

[Joe Walsh]

Hi Pauleyb

I have attatched a workbook showing what I was trying to explain, I have yet to figure out how to concatenate entire rows if they share the same ID code. THe first sheet is a copy of a worksheet where that Vba userform inserts data and it sorted by time, the second sheet is what I am hoping to accomplish aka concatenate whole rows that share the same ID Code. Sorry for the confusion.
Also the TimeStamp on the second sheet is a little messy..

Thanks for the Help!

[Pauleyb]

Yeah, you are now venturing into needing VBA. The base excel formulas are not able to do essentially a VLOOKUP for the nth element in a list (although there is a hidden unsupported function) and also with an variable n and to concatenate into one cell.

[Joe Walsh]

Ok Thanks Pauleyb I will try posting a new thread in the VBA section