# Compile list of number

#### Davers

##### Well-known Member
Hey gang! It's been awhile! I have a list of about 6800 names in column A. In columns B:BK are numbers. For each name in column A, I need to get that list of numbers in B:BK and put it on a new sheet all in Column A. So for instance, if A2 is Dave, and B2 = 654 and D2 = 987, I need Column A and B in a new sheet to be:
Dave 654
Dave 987

Does that make sense? The entire list from the original sheet needs to be in a new sheet all in Column A with the results in column B...

I hope I'm explaining myself well enough. Thanks for any help!!

Dave

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
EDIT: Sorry - Will each row have unique numbers? Or can you have 654, 987, and 654 again?

Last edited:
Hey Ben! The numbers can repeat. There will not be a blank cell in column A, but there can be blanks in B:BK. Sometimes cells are skipped for no good reason..

Thanks!

Dave

And I'm assuming you don't want blanks in the new sheet .. you want it to keep pulling the next number; is that right?

Ok, got it. On Sheet2, put this in A1. Drag Across and down. =IFERROR(Sheet1!\$A1&" "&INDEX(Sheet1!\$B1:\$H1,SMALL(IF(Sheet1!\$B1:H1<>"",COLUMN(Sheet1!\$B1:H1)),COLUMN(A1))-1),"")

For Excel 2003 and lower, use =IF(ISERROR(Sheet1!\$A1&" "&INDEX(Sheet1!\$B1:\$H1,SMALL(IF(Sheet1!\$B1:H1<>"",COLUMN(Sheet1!\$B1:H1)),COLUMN(A1))-1)),"",Sheet1!\$A1&" "&INDEX(Sheet1!\$B1:\$H1,SMALL(IF(Sheet1!\$B1:H1<>"",COLUMN(Sheet1!\$B1:H1)),COLUMN(A1))-1))

Please note - these are array formulas and must be entered with CTRL+SHIFT+ENTER

Also, I did it from B1:H1 - change that to B1:BK1

Did this work for you?

Hey Ben, this did work for me! I apologize, I thought I answered this long time ago!

Thanks again!

Dave

No problem Glad I could help Appreciate the feedback!

Replies
5
Views
49
Replies
1
Views
87
Replies
20
Views
579
Replies
7
Views
143
Replies
4
Views
125

1,203,396
Messages
6,055,158
Members
444,766
Latest member
bryandaniel5

### 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