Rows to column according to specific text

D

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)?



[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]STREET NAME[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OTHER STREET NAME[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AVENUE NAME[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[TD]000000-U-0000[/TD]
[/TR]
</tbody>[/TABLE]

Regards.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Custom.1.1[/td][td=bgcolor:#70AD47]Custom.1.2[/td][td=bgcolor:#70AD47]Custom.1.3[/td][td=bgcolor:#70AD47]Custom.1.4[/td][td=bgcolor:#70AD47]Custom.1.5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]STREET NAME[/td][td][/td][td=bgcolor:#E2EFDA]STREET NAME[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]000000-U-0000[/td][td][/td][td]OTHER STREET NAME[/td][td]000000-U-0000[/td][td]000000-U-0000[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]000000-U-0000[/td][td][/td][td=bgcolor:#E2EFDA]AVENUE NAME[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][td=bgcolor:#E2EFDA]000000-U-0000[/td][/tr]

[tr=bgcolor:#FFFFFF][td]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]OTHER STREET NAME[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]AVENUE NAME[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]000000-U-0000[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
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
You must log in or register to reply here.

Similar threads

PuntingJawa
  • Solved
If column B has # then pull data from column A beside it
Replies
4
Views
223
PuntingJawa
PuntingJawa
P
  • Solved
Dynamic Subtotal of column after each row entry has been multiplied by two-way lookup
Replies
4
Views
268
KRice
K
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
122
mumps
mumps
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
390
ChrisMac1
ChrisMac1
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
858
Micron
M

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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