Split a range of numbers in 2 columns into rows

mebadwow

New Member
Joined
Mar 13, 2018
Messages
3
I have an excel spreadsheet that has a range of data that needs separates into individual rows. I have researched this endlessly and have not found a solution.

MN
30429300203042930044
3042930045
30429300463042930074
30429300763042930080
3042930081
30429300823042930092
30429300943042930102
30429301043042930106

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</tbody>

How would I separate line 10 M & N into separate rows?
3042930020
3042930021...etc
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I have an excel spreadsheet that has a range of data that needs separates into individual rows. I have researched this endlessly and have not found a solution.

MN
30429300203042930044
3042930045
30429300463042930074
30429300763042930080
3042930081
30429300823042930092
30429300943042930102
30429301043042930106

<tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</tbody>

How would I separate line 10 M & N into separate rows?
3042930020
3042930021...etc

Are you attempting to transpose or do something else? Can you put and example of what you're looking to do?
 
Upvote 0
Are you attempting to transpose or do something else? Can you put and example of what you're looking to do?

Sorry I thought I had that info in there:

Line 10 would look like:
3042930020
3042930021
3042930022
3042930023
3042930024
3042930025
and so on through 3042930044
 
Upvote 0
Hi,

Do you mean something like this?


Book1
MNO
10304293002030429300443042930020
113042930021
123042930022
133042930023
143042930024
153042930025
163042930026
173042930027
183042930028
193042930029
203042930030
213042930031
223042930032
233042930033
243042930034
253042930035
263042930036
273042930037
283042930038
293042930039
303042930040
313042930041
323042930042
333042930043
343042930044
Sheet20
Cell Formulas
RangeFormula
O10=M10
O11=IFERROR(IF(O10+1>N$10,"",O10+1),"")


O11 formula copied down.
 
Upvote 0
Hi,

Do you mean something like this?

MNO
304293002030429300443042930020
3042930021
3042930022
3042930023
3042930024
3042930025
3042930026
3042930027
3042930028
3042930029
3042930030
3042930031
3042930032
3042930033
3042930034
3042930035
3042930036
3042930037
3042930038
3042930039
3042930040
3042930041
3042930042
3042930043
3042930044

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet20

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O10[/TH]
[TD="align: left"]=M10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O11[/TH]
[TD="align: left"]=IFERROR(IF(O10+1>N$10,"",O10+1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



O11 formula copied down.

Yes that would work. So I assume I would need to at least at the empty rows before using that formula?
 
Upvote 0
Yes, you need enough empty rows to cover your range, you can copy O11 formula further than needed, formula will result in blank when it exceeds N10.
 
Upvote 0

Forum statistics

Threads
1,223,054
Messages
6,169,834
Members
452,284
Latest member
TKM623

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
Back
Top