Rows to column according to specific text

danielhvf

New Member
Joined
Nov 22, 2013
Messages
4
Hi,

I've a serie of data with this configuration:

Column A
STREET NAME
000000-U-0000
000000-U-0000
000000-U-0000
<empty cell>
OTHER STREET NAME
000000-U-0000
000000-U-0000
<empty cell>
AVENUE NAME
000000-U-0000
000000-U-0000
000000-U-0000
000000-U-0000
000000-U-0000

Is it possible to transpose the data under the "street name" to the Columns B, C, D (and so on)?



ABCDEF
STREET NAME000000-U-0000000000-U-0000000000-U-0000
OTHER STREET NAME000000-U-0000000000-U-0000
AVENUE NAME000000-U-0000000000-U-0000000000-U-0000000000-U-0000000000-U-0000

<tbody>
</tbody>

Regards.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Just select STREET NAME + 3 cells below. Copy them (CTRL+C). Go to another worksheet and right click on a a cell where you want to paste transposed data.
From context menu select "Paste special" and then check Transpose and click OK.

Select OTHER STREET NAME + 2 cells below and do the same.

Do the same for other streets(ranges)
 
Last edited:
Upvote 0
My file has 70259 lines, corresponding to 10750 street names and the remaining to every house in each street (000000-U-0000).
Not human doable that way...
 
Upvote 0
If you have empty cells between each section try
Code:
Sub TransData()
   Dim rng As Range
   
   For Each rng In Range("A:A").SpecialCells(xlConstants).Areas
      rng.Resize(, rng.Count).Value = Application.Transpose(rng)
      rng.Offset(1).Resize(rng.Count - 1).Clear
   Next rng
   Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Upvote 0
Hi, danielhvf
Assuming each group of data is separated by blank row
Here's just another way:

Code:
[COLOR=blue]Sub[/COLOR] a1074344a[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1074344-rows-column-according-specific-text.html[/COLOR][/I]
[COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] j [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] k [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] va [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR][B],[/B] vb [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
 
rr [B]=[/B] Range[B]([/B][COLOR=brown]"A:A"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"*"[/COLOR][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
va [B]=[/B] Range[B]([/B][COLOR=brown]"A1:A"[/COLOR] [B]&[/B] rr[B])[/B]
 
    [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
        [COLOR=blue]If[/COLOR] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] [COLOR=brown]""[/COLOR] [COLOR=blue]Then[/COLOR] k [B]=[/B] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
    [COLOR=blue]Next[/COLOR]
 
[COLOR=blue]ReDim[/COLOR] vb[B]([/B][B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
k [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B]
    [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
        [COLOR=blue]If[/COLOR] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]<>[/B] [COLOR=brown]""[/COLOR] [COLOR=blue]Then[/COLOR]
        j [B]=[/B] j [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
        vb[B]([/B]j[B],[/B] k[B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
        [COLOR=blue]Else[/COLOR]
        j [B]=[/B] [B][COLOR=crimson]0[/COLOR][/B]
        k [B]=[/B] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
Range[B]([/B][COLOR=brown]"D1"[/COLOR][B]).[/B]Resize[B]([/B]UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]2[/COLOR][/B][B]))[/B] [B]=[/B] vb
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
Upvote 0
I'd like to see more representative example but...
you can try with PowerQuery (aka Get&Transform)

Column1CustomCustom.1.1Custom.1.2Custom.1.3Custom.1.4Custom.1.5
STREET NAMESTREET NAME000000-U-0000000000-U-0000000000-U-0000
000000-U-0000OTHER STREET NAME000000-U-0000000000-U-0000
000000-U-0000AVENUE NAME000000-U-0000000000-U-0000000000-U-0000000000-U-0000000000-U-0000
000000-U-0000
OTHER STREET NAME
000000-U-0000
000000-U-0000
AVENUE NAME
000000-U-0000
000000-U-0000
000000-U-0000
000000-U-0000
000000-U-0000
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if not Text.Contains([Column1], "-") then [Column1] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Custom"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1] = [Custom] then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column1", each ([Custom.1] = null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Column1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", type text}, {"Custom.1.2", type text}, {"Custom.1.3", type text}, {"Custom.1.4", type text}, {"Custom.1.5", type text}})
in
    #"Changed Type1"[/SIZE]
 
Upvote 0
Sorry, I just realized I got something wrong in my previous code.
Use this one instead:

Code:
[COLOR=blue]Sub[/COLOR] a1074344b[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1074344-rows-column-according-specific-text.html[/COLOR][/I]
[COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] j [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] k [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] rr [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] va [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR][B],[/B] vb [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
 
rr [B]=[/B] Range[B]([/B][COLOR=brown]"A:A"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"*"[/COLOR][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
va [B]=[/B] Range[B]([/B][COLOR=brown]"A1:A"[/COLOR] [B]&[/B] rr[B])[/B]
 
    [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
        [COLOR=blue]If[/COLOR] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] [COLOR=brown]""[/COLOR] [COLOR=blue]Then[/COLOR] k [B]=[/B] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
    [COLOR=blue]Next[/COLOR]
 
[COLOR=blue]ReDim[/COLOR] vb[B]([/B][B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] [B][COLOR=crimson]10000[/COLOR][/B][B])[/B]
k [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B]
    [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
        [COLOR=blue]If[/COLOR] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]<>[/B] [COLOR=brown]""[/COLOR] [COLOR=blue]Then[/COLOR]
            j [B]=[/B] j [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
            vb[B]([/B]k[B],[/B] j[B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
            [COLOR=blue]Else[/COLOR]
            j [B]=[/B] [B][COLOR=crimson]0[/COLOR][/B]
            k [B]=[/B] k [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR]
Range[B]([/B][COLOR=brown]"D1"[/COLOR][B]).[/B]Resize[B]([/B]UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]2[/COLOR][/B][B]))[/B] [B]=[/B] vb
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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