Transforming Single Column data to Range/Rows

A

agentx95

New Member
Joined
Apr 4, 2019
Messages
4
Hi, i am new here. I need help for this, so I have a long list of data and I need to convert/transform in into comprehensible range or rows. Excel will start a new row after each blank in the column.

like this:

data:

Ref#XXX
Acct#XXX
Desc
Amnt
Acct#XXX
Desc
Amnt
-blank-
Ref#XXX
Acct#XXX
Desc
Amnt
Acct#XXX
Desc
Amnt
Acct#XXX
Desc
Amnt
-blank-
Ref#XXX
Acct#XXX
Desc
Amnt
Acct#XXX
Desc
Amnt


to this output,

Ref#XXX Acct#XXX Desc Amt Acct#XXX Desc Amt
Ref#XXX Acct#XXX Desc Amt Acct#XXX Desc Amt Acct#XXX Desc Amt
Ref#XXX Acct#XXX Desc Amt Acct#XXX Desc Amt

Thanks for any help.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi agentx95,

Welcome to MrExcel!!

Try this macro while on the sheet in question:

Code: 
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    Dim lngPasteCol As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    lngPasteRow = 2 'Initial output Row. Change to suit.
    
    For lngMyRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'Works from Row 2 down to the last Row in Col. A. Change to suit.
        If Len(Range("A" & lngMyRow)) > 0 Then
            lngPasteCol = Cells(lngPasteRow, Columns.Count).End(xlToLeft).Column + 1
            Cells(lngPasteRow, lngPasteCol).Value = Range("A" & lngMyRow).Value
        Else
            lngPasteRow = lngPasteRow + 1
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]source[/td][/tr]

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

[tr=bgcolor:#FFFFFF][td]Acct#XXX[/td][/tr]

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

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

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

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

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

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

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

[tr=bgcolor:#FFFFFF][td]Acct#XXX[/td][/tr]

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

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

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

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

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

[tr=bgcolor:#FFFFFF][td]Acct#XXX[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td]Ref#XXX[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td]Acct#XXX[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td]Ref#XXX[/td][/tr]

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

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

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

you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]result.1[/td][td=bgcolor:#70AD47]result.2[/td][td=bgcolor:#70AD47]result.3[/td][td=bgcolor:#70AD47]result.4[/td][td=bgcolor:#70AD47]result.5[/td][td=bgcolor:#70AD47]result.6[/td][td=bgcolor:#70AD47]result.7[/td][td=bgcolor:#70AD47]result.8[/td][td=bgcolor:#70AD47]result.9[/td][td=bgcolor:#70AD47]result.10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ref#XXX[/td][td=bgcolor:#E2EFDA]Acct#XXX[/td][td=bgcolor:#E2EFDA]Desc[/td][td=bgcolor:#E2EFDA]Amnt[/td][td=bgcolor:#E2EFDA]Acct#XXX[/td][td=bgcolor:#E2EFDA]Desc[/td][td=bgcolor:#E2EFDA]Amnt[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ref#XXX[/td][td]Acct#XXX[/td][td]Desc[/td][td]Amnt[/td][td]Acct#XXX[/td][td]Desc[/td][td]Amnt[/td][td]Acct#XXX[/td][td]Desc[/td][td]Amnt[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ref#XXX[/td][td=bgcolor:#E2EFDA]Acct#XXX[/td][td=bgcolor:#E2EFDA]Desc[/td][td=bgcolor:#E2EFDA]Amnt[/td][td=bgcolor:#E2EFDA]Acct#XXX[/td][td=bgcolor:#E2EFDA]Desc[/td][td=bgcolor:#E2EFDA]Amnt[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ref#XXX[/td][td]Acct#XXX[/td][td]Desc[/td][td]Amnt[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    Trans = Table.Transpose(Source),
    MergeCols = Table.CombineColumns(Table.TransformColumnTypes(Trans, {{"Column8", type text}, {"Column19", type text}, {"Column27", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"result"),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(MergeCols, {{"result", Splitter.SplitTextByDelimiter(",,", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "result"),
    SplitToCols = Table.SplitColumn(SplitToRows, "result", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"result.1", "result.2", "result.3", "result.4", "result.5", "result.6", "result.7", "result.8", "result.9", "result.10"})
in
    SplitToCols[/SIZE]
 
Last edited:
Upvote 0
Does this macro do what you want...
Code: 
Sub RearrangeData()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(1).Resize(, Ar.Count) = Application.Transpose(Ar)
  Next
  Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Upvote 0
It worked. Thank you so much...



Trebor76 said:
Hi agentx95,

Welcome to MrExcel!!

Try this macro while on the sheet in question:

Code: 
Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim lngPasteRow As Long
    Dim lngPasteCol As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    lngPasteRow = 2 'Initial output Row. Change to suit.
    
    For lngMyRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'Works from Row 2 down to the last Row in Col. A. Change to suit.
        If Len(Range("A" & lngMyRow)) > 0 Then
            lngPasteCol = Cells(lngPasteRow, Columns.Count).End(xlToLeft).Column + 1
            Cells(lngPasteRow, lngPasteCol).Value = Range("A" & lngMyRow).Value
        Else
            lngPasteRow = lngPasteRow + 1
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
Click to expand...
 
Upvote 0
This worked for me too! Thank you very much. Been at this for weeks.


Rick Rothstein said:
Does this macro do what you want...
Code: 
Sub RearrangeData()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(1).Resize(, Ar.Count) = Application.Transpose(Ar)
  Next
  Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA to amend report format based on unique values
Replies
1
Views
34
JoeRooney
J
S
  • Solved
QueryTables bug. If one query fails, all subsequent queries will fail unless restart Excel
Replies
4
Views
454
Micron
M
B
  • Question Question
Add new Row in ListObject Table with data from an Array
Replies
4
Views
1K
Akuini
Akuini
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
L
  • Question Question
VBA Code to add in blank rows at a specific point, then do a formula in that blank row
Replies
1
Views
825
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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