Dividing information into rows

K

kovace11

New Member
Joined
May 16, 2011
Messages
5
Hey guys,

I have a large excel file. Currently the information is in one column like so:


..................HAT
car.............HAT
..................HAT

but I want it to look like:


car................HAT
car................HAT
car................HAT



I need an efficient way of doing this because there are many subdivisions and there are alot of rows (thousands).

Any ideas?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
this is a hybrid solutions.

your data like this from A1 to B7(one more set added for check

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} table.MsoTableGrid {mso-style-name:"Table Grid"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-border-insideh:.5pt solid windowtext; mso-border-insidev:.5pt solid windowtext; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:480;mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-border-insideh: .5pt solid windowtext;mso-border-insidev:.5pt solid windowtext" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:221.4pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> hdng1
</td> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> hdng2
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295">
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> hat
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> car
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> hat
</td> </tr> <tr style="mso-yfti-irow:3"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295">
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> hat
</td> </tr> <tr style="mso-yfti-irow:4"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295">
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> rat
</td> </tr> <tr style="mso-yfti-irow:5"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295">
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> rat
</td> </tr> <tr style="mso-yfti-irow:6;mso-yfti-lastrow:yes"> <td style="width:221.4pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> Train
</td> <td style="width:221.4pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="295"> rat
</td> </tr> </tbody></table>
now run this macro

Code: 
Sub find_sets()
Dim r As Range, c As Range
Set r = Range(Range("A2"), Cells(Rows.Count, "B").End(xlUp).Offset(0, -1))
For Each c In r
If c <> "" Then
Range(c, c.Offset(0, 1)).Copy Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
End If
Next
Range("A1:B1").Copy Range("G1")
End Sub
Code: 
you will get from G1 to H3 as follows

<!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-parent:""; 	mso-padding-alt:0in 5.4pt 0in 5.4pt; 	mso-para-margin:0in; 	mso-para-margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:10.0pt; 	font-family:"Times New Roman"; 	mso-ansi-language:#0400; 	mso-fareast-language:#0400; 	mso-bidi-language:#0400;} </style> <![endif]-->  <table class="MsoNormalTable" style="width:96.0pt;margin-left:4.65pt;border-collapse:collapse;mso-padding-alt:  0in 5.4pt 0in 5.4pt" width="128" border="0" cellpadding="0" cellspacing="0">  <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hdng1[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hdng2[/FONT][FONT=Arial][/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:1;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]car[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:2;mso-yfti-lastrow:yes;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]Train[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]rat[/FONT]
   </td>  </tr> </tbody></table>  
now goto A2 and type this formula 

=INDEX($G$2:$G$100,MATCH(B2,$H$2:$H$100,0),1)

copy A2 down.

you will get 

<!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-parent:""; 	mso-padding-alt:0in 5.4pt 0in 5.4pt; 	mso-para-margin:0in; 	mso-para-margin-bottom:.0001pt; 	mso-pagination:widow-orphan; 	font-size:10.0pt; 	font-family:"Times New Roman"; 	mso-ansi-language:#0400; 	mso-fareast-language:#0400; 	mso-bidi-language:#0400;} </style> <![endif]-->  <table class="MsoNormalTable" style="width:96.0pt;margin-left:4.65pt;border-collapse:collapse;mso-padding-alt:  0in 5.4pt 0in 5.4pt" width="128" border="0" cellpadding="0" cellspacing="0">  <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hdng1[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hdng2[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:1;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]car[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:2;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]car[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:3;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]car[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]hat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:4;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]Train[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]rat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:5;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]Train[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]rat[/FONT]
   </td>  </tr>  <tr style="mso-yfti-irow:6;mso-yfti-lastrow:yes;height:12.75pt">   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]Train[/FONT]
   </td>   <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt;   height:12.75pt" valign="bottom" width="64" nowrap="nowrap">   [FONT=Arial]rat[/FONT]
   </td>  </tr> </tbody></table>  

is it ok?(upto 100 rows in colA and B. if more modify the formula
 
Upvote 0
OP stated that the data was in one column.

Kovace11, are there always exactly 13 or 18 dots in each cell?

We may need more sample data and expected results.
 
Upvote 0
Hey guys,

Thanks for the help so far.

Sorry i wasn't clear.

There are not dots. I just used the dots so I can space things out properly. There are only 2 columns. The first column has the 3 rows merged while the second column has 3 unmerged rows. I want to unmerge the first column so that each row has the same value as the original merged column. If thats clear?

Thanks
 
Upvote 0
Like this?

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 45pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=60 rowSpan=3 width=64>
car​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>
HAT​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>
HAT​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>
HAT​
</TD></TR></TBODY></TABLE>

To be fair, you did say "the information is in one column". Being able to see the data helps!

I think I have a piece of code which delas with that sort of thing... hang on...
 
Upvote 0
So Venkat's code should work. Did you try it? Please do.

If that doesn't do what you want, try this:-
Code: 
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Public Sub CopyHeadingsDown()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  Dim iLastRow As Long
  Dim iRow As Long
  Dim sHeading As String
  
  iLastRow = Cells(Rows.Count, 2).End(xlUp).Row
  
  Range("A1:A" & iLastRow).UnMerge
  
  For iRow = [B][COLOR=red]1[/COLOR][/B] To iLastRow
    If IsEmpty(Cells(iRow, 1)) Then
      Cells(iRow, 1) = sHeading
    Else
      sHeading = Cells(iRow, 1)
    End If
  Next iRow
  
End Sub[/FONT]
Change the red bit to point at the first row of your data.

And test on a copy of your worksheet!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Automatic Date
Replies
5
Views
334
Jlopez0320
J
R
  • Question Question
Trying to create a line chart in Excel with NO experience.
Replies
1
Views
242
Rhothgar
R
crook_101
  • Solved
VB to loop
Replies
5
Views
231
myall_blues
myall_blues
crook_101
  • Question Question
VBA to loop when sending email
Replies
1
Views
259
crook_101
crook_101
B
  • Solved
change summary from daily to monthly
Replies
2
Views
639
breilly00
B

Forum statistics

Threads
1,224,570
Messages
6,179,610
Members
452,931
Latest member
The Monk

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