Split a range of numbers in 2 columns into rows

M

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
Sort by date Sort by votes
mebadwow said:
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
Click to expand...

Are you attempting to transpose or do something else? Can you put and example of what you're looking to do?
 
Upvote 0
Melvin88 said:
Are you attempting to transpose or do something else? Can you put and example of what you're looking to do?
Click to expand...

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
jtakw said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

T
  • Question Question
send separate email each time a change is found in a range of cells
Replies
0
Views
358
timlh42
T
M
  • Question Question
Make combinations from 2 sets of numbers
Replies
5
Views
589
shg
shg
A
  • Question Question
VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook
Replies
6
Views
740
annuity
A
B
  • Question Question
Comparing 2 Worksheets and Updating Rows Where Differences were Found
2
Replies
13
Views
2K
Fazza
F

Forum statistics

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

Share this page

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