how do I split contents of a cell into separate rows and carry over the data in other columns to each new row?

R

redchelle123

New Member
Joined
Oct 11, 2010
Messages
6
Hi there,

In column A I have locality names (ranges from 5 to 20 localities) separated by a line feed (Alt Enter). Columns B to G contain a number or word that relates to the group of localities in column A.
I want to split the localities into separate rows and copy the values in columns B to G.
I found a macro at (http://www.mrexcel.com/forum/showthread.php?t=345162) that split the localities successfully but it did not carry the other column values to the new rows.

Any help would be greatly appreciated.

Thanks in advance! :)
Rachelle.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
redchelle123,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum. Two reasons:

- helpers don't have to spend extra time tracking to other sites to find your samples.
- you can post samples here where helpers can copy your sample data to their own test worksheets. Most of us don't like manually typing sample data!

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or

RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or

Borders-Copy-Paste
 
Upvote 0
hi again,
I am using Excel 2003.
I've been unable to post a sample of my data - even with your help, hiker95.

The best I can do is:

<table border="0" cellpadding="0" cellspacing="0" width="843"><col style="width: 163pt;" width="217"> <col style="width: 37pt;" width="49"> <col style="width: 101pt;" width="135"> <col style="width: 95pt;" width="126"> <col style="width: 82pt;" width="109"> <col style="width: 83pt;" width="110"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl31" style="height: 12.75pt; width: 163pt;" height="17" width="217">What I currently have:</td> <td class="xl25" style="width: 37pt;" width="49">
</td> <td class="xl25" style="width: 101pt;" width="135">
</td> <td class="xl25" style="width: 95pt;" width="126">
</td> <td class="xl25" style="width: 82pt;" width="109">
</td> <td class="xl25" style="width: 83pt;" width="110">
</td> <td class="xl25" style="width: 73pt;" width="97">
</td> </tr> <tr style="height: 76.5pt;" height="102"> <td class="xl24" style="height: 76.5pt; width: 163pt;" height="102" width="217">Red Hill
Curtin
Farrer
Monash
Weston
</td> <td class="xl25">ACT</td> <td class="xl26" style="width: 101pt;" width="135">Canberra</td> <td class="xl27">16</td> <td class="xl27">18</td> <td class="xl28" style="width: 83pt;" width="110">34</td> <td class="xl29" style="width: 73pt;" width="97">198,245.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 163pt;" height="17" width="217">
</td> <td class="xl25">
</td> <td class="xl26" style="width: 101pt;" width="135">
</td> <td class="xl27">
</td> <td class="xl27">
</td> <td class="xl28" style="width: 83pt;" width="110">
</td> <td class="xl29" style="width: 73pt;" width="97">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl31" style="height: 12.75pt;" height="17">What I hope to achieve:</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Red Hill</td> <td class="xl25">ACT</td> <td class="xl25">Canberra</td> <td class="xl25">16</td> <td class="xl25">18</td> <td class="xl25">34</td> <td class="xl30">198,245.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Curtin</td> <td class="xl25">ACT</td> <td class="xl25">Canberra</td> <td class="xl25">16</td> <td class="xl25">18</td> <td class="xl25">34</td> <td class="xl30">198,245.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Farrer</td> <td class="xl25">ACT</td> <td class="xl25">Canberra</td> <td class="xl25">16</td> <td class="xl25">18</td> <td class="xl25">34</td> <td class="xl30">198,245.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Monash</td> <td class="xl25">ACT</td> <td class="xl25">Canberra</td> <td class="xl25">16</td> <td class="xl25">18</td> <td class="xl25">34</td> <td class="xl30">198,245.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">Weston</td> <td class="xl25">ACT</td> <td class="xl25">Canberra</td> <td class="xl25">16</td> <td class="xl25">18</td> <td class="xl25">34</td> <td class="xl30">198,245.00</td> </tr> </tbody></table>
 
Upvote 0
redchelle123,


Sample raw data in worksheet Sheet1 beginning in cell A1:


Excel Workbook
ABCDEFG
1Red Hill Curtin Farrer Monash WestonACTCanberra161834198,245.00
2
Sheet1





After the macro in a new worksheet Results:


Excel Workbook
ABCDEFG
1Red HillACTCanberra161834198,245.00
2CurtinACTCanberra161834198,245.00
3FarrerACTCanberra161834198,245.00
4MonashACTCanberra161834198,245.00
5WestonACTCanberra161834198,245.00
6
Results





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub SplitData()
' hiker95, 10/11/2010, ME500943
Dim w1 As Worksheet, wR As Worksheet
Dim c As Range, Sp
Dim NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
NR = 1
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
  Sp = Split(Trim(c), Chr(10))
  wR.Range("A" & NR).Resize(UBound(Sp)) = Application.Transpose(Sp)
  wR.Range("B" & NR).Resize(UBound(Sp), 6).Value = w1.Range("B" & c.Row & ":G" & c.Row).Value
  wR.Range("G" & NR).Resize(UBound(Sp), 6).NumberFormat = "#,##0.00"
  NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Next c
wR.Columns("A:G").AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub


Then run the SplitData macro.
 
Upvote 0
redchelle123,

If the above code does not work correctly can we have another workbook with more samples of your raw data in Sheet1?
 
Upvote 0
Thanks for the macro - I ran it and it worked for the first 18 rows (there are 151 in total) .
I also got a run-time error '1004': Application-defined or object-defined error. When I debugged, it went to the line:
wR.Range("A" & NR).Resize(UBound(Sp)) = Application.Transpose(Sp)

I have put more sample data (test_data2) here:
http://www.box.net/files#/files/0/f/0

Again, thanks for the time taken.
 
Upvote 0
I noticed that there were 2 instances of a single locality in a row, which I think caused the error. I took them out to see if that helped and i got success.

Thanks for your help on this hiker95 I really appreciate it.
 
Upvote 0
redchelle123,

The latest link to your new workbook on box.net is requesting an ID and Password.

Do you want to try again to post more raw data?

I can then adjust the macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
193
bobaol
B
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
399
AndyPandy27
A
J
  • Question
Importing Outlook Data
Replies
0
Views
368
Jack2010
J
A
  • Solved
Inserting new rows based on cell value, and copying data to the new rows
Replies
5
Views
1K
Andrew87
A
G
  • Solved
How to split text and merge in new row?
Replies
4
Views
258
genetist
G

Forum statistics

Threads
1,214,606
Messages
6,120,490
Members
448,967
Latest member
visheshkotha

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