# Merge rows based on row label unique value

#### rose_chris

##### New Member
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
789 cfc
789 dfa
901 dak

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
901 dak

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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

Replies
11
Views
885
Replies
6
Views
238
Replies
2
Views
942
Replies
7
Views
188
Replies
1
Views
472

1,196,184
Messages
6,013,919
Members
441,795
Latest member
Lilium

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back