Compile list of number

[Davers]

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

 

[BenMiller]

EDIT: Sorry - Will each row have unique numbers? Or can you have 654, 987, and 654 again?

 

[Davers]

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

 

[BenMiller]

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?

 

[BenMiller]

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

 

[BenMiller]

Did this work for you?

 

[Davers]

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

Thanks again!

Dave

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!