Compile list of number

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

BenMiller

Well-known Member
Joined
Nov 17, 2011
Messages
1,961
Office Version
  1. 365
Platform
  1. Windows
EDIT: Sorry - Will each row have unique numbers? Or can you have 654, 987, and 654 again?
 
Last edited:

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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

Well-known Member
Joined
Nov 17, 2011
Messages
1,961
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 17, 2011
Messages
1,961
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Hey Ben, this did work for me! I apologize, I thought I answered this long time ago!

Thanks again!

Dave
 

BenMiller

Well-known Member
Joined
Nov 17, 2011
Messages
1,961
Office Version
  1. 365
Platform
  1. Windows
No problem :) Glad I could help (y) Appreciate the feedback!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,861
Members
414,408
Latest member
macroSmith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top