Merge rows based on row label unique value

[rose_chris]

I’m having a list of more than 100,000 data. First column has item number, second column has the notes that are associated with it. One item number might have many notes.
Currently the data is like this
item note
123 abc
123 cdf
456 adad
789 cfc
789 dfa
789 ada
901 dak
234 kad

I’d like to merge the notes to associate with one item number, so that I can display it in one row. The issue is, I have so many records and the number of notes rows varies, so I can’t use concatenate or merge rows manually.

I’d like the end result like this:
123 abc, cdf
456 adad
789 cfc, dfa, ada
901 dak
234 kad

Is there a way I can do this without using vba? I tried tabular design for Pivot but it still shows as separate rows for each unique item number.

 

[Mike Szczesny]

Assume your column data for item numbers is A2:A10 and B2:B10 for note items. You first need to determine unique item numbers. Place this formula in C2
C2: =INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($C$2:C2))) Use Control-Shift-Enter, copy down

Now we need to list the all the note items for each item. Place this formula in D2 to list note items on the same row
D2: =INDEX($B$2:$B$10,SMALL(IF(FREQUENCY(IF($B$2:$B$10<>"",IF($A$2:$A$10=$C$2,MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-ROW($B$2)+1),ROW($B$2:$B$10)-ROW($B$2)+1),COLUMNS($D$2:D2))) Use control-shift-enter copy across then down You may want to make sure your absolute references are correct for the proper copying of formulas