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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

nardagus

Active Member
Joined
Apr 23, 2012
Messages
314
Office Version
2016, 2013
Platform
Windows
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:

danielhvf

New Member
Joined
Nov 22, 2013
Messages
4
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...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,637
Office Version
365
Platform
Windows
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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]
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,318
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]
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,099,046
Messages
5,466,243
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top