Convert Excel Range to Rows

[raltam]

Hello,I am trying to figure out a way to create one big master list that lists all of the accounts within the given range.

I have about 1000 rows with different variation of ranges (1st example below) and for each range listed below, I need to list each account within that range in column D and then getting it to do the same for each different range. For example, for my example below, the 1st range is from 70000 to 71000, I want to list all accounts within that range in column D (including the beginning and ending account rage). So for the 1st range, I should ultimately have 1000 rows and continue on for every range there after. If the range is 70000 to 70001 then it would list two object accounts and so on. I think you guys get the point.

At the end I would end up with four columns like in the second example below and will have thousands of rows for the different ranges.

I started doing this manually and trying some IF scenarios, but I haven't been successful and this would obviously take me forever to do it manually.

Any help is greatly appreciated. I did look around and I didn't quite find something that would help me with this, so thanks in advance for your help!!



[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Key
(Col A)
[/TD]
[TD]Account From
(Col B)
[/TD]
[TD]Account To
(Col C)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 73"]
<tbody>[TR]
[TD]72000 :74000
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]72000
[/TD]
[TD]74000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]72000 :74900
[/TD]
[TD]72000
[/TD]
[TD]74900
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Key
(A)
[/TD]
[TD]Account From
(B)
[/TD]
[TD]Account To
(C)
[/TD]
[TD]List Each Account
(D)
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70000
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70001
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70002
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70003
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70004
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70005
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70006
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70007
[/TD]
[/TR]
[TR]
[TD]70000 :71000
[/TD]
[TD]70000
[/TD]
[TD]71000
[/TD]
[TD]70008
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B	C	D
1	Key	From	To
2	70:78	70	78	9
3	79:85	79	85	7
4	86:97	86	97	12
5
6	70	0	70
7	70	1	71
8	70	2	72
9	70	3	73
10	70	4	74
11	70	5	75
12	70	6	76
13	70	7	77
14	70	8	78
15	79	0	79
16	79	1	80
17	79	2	81
18	79	3	82
19	79	4	83
20	79	5	84
21	79	6	85
Sheet1

Cell Formulas
Range	Formula
D2	=C2-B2+1
A6	=LOOKUP(ROW(B1),SUMIF(OFFSET(D$1,,,ROW($1:$99),),"<>")+1,B$2:B$99)&""
B6	=COUNTIF($A$6:A6,A6)-1
C6	=A6+B6

The ranges can be adjusted for your data

 

[Dryver14]

Hi Formula seems to be the answer looking at above, however this gives you the option to input first and last number then fills the series for you

Sub KWCreateManyNumbers()


    Dim TopNumber As Long
    Dim BottomNumber As Long
    Dim i As Long
    Dim NextRow As Long


    
    NextRow = Cells(Rows.Count, 4).End(xlUp).Row + 1
    
    TopNumber = InputBox(Prompt:="Please enter the first Number in the range", Title:="Select first number")
    BottomNumber = InputBox(Prompt:="Please enter the Last Number in the range", Title:="Select Last number")
    
    Cells(NextRow, 4).Select
    
    For i = TopNumber To BottomNumber
        Cells(NextRow, 4) = i
        Cells(NextRow, 1) = TopNumber & " " & ":" & " " & BottomNumber
        Cells(NextRow, 2) = TopNumber
        Cells(NextRow, 3) = BottomNumber
        NextRow = NextRow + 1
    Next i
    
    
    
End Sub

 

[raltam]

Sorry.. I should have been more clear, but I need the key to be listed on each row also.

I am not at home at the moment to try it, but will this also carry the "key" column also?

As additional info, once I compile this huge master list with all the ranges and accounts in four columns (Key, Account From, Account To, and Individual Object account rows), I am then going to use that key to link it to another table so I can then pull in the list of accounts for each range whenever there's a one to one match. That's why I need it all in one worksheet and four columns listing all four columns. Sorry for the confusion. Thanks again!

 

[Dryver14]

The Macro does all this

 

[raltam]

Ok... I could give that a try when I get home.

Is there a way to point the macro to my existing list of ranges with keys and have the macro automatically create the series for each of the 1000 ranges that I have listed - one after another? or do I have to enter the values for each range?

 

[sheetspread]

will this also carry the "key" column also?

Yes, just add another column and change the B in the formula to A or wherever you have the key. A formula can also split the key along the : if you were manually doing that before.

 

[Dryver14]

If you want to stick with code just do as it states above, yes I could probably replace the code if I understood where your list of requirements are and how they look on the sheet

 

[raltam]

I tried, but it did not seem to work for me. I was able to carry the formula and it worked for the first range, but it didn't change as it moved to the next range. I sent you a PM. Thanks for your help!

 

[raltam]

Yes, just add another column and change the B in the formula to A or wherever you have the key. A formula can also split the key along the : if you were manually doing that before.

I tried, but it did not seem to work for me. I was able to carry the formula and it worked for the first range, but it didn't change as it moved to the next range. I sent you a PM. Thanks for your help!