Transforming Single Column data to Range/Rows

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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...



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
This worked for me too! Thank you very much. Been at this for weeks.


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

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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