Transposing cell contents from one sheet into another sheet with different order/format

kidneythief

New Member
Joined
Mar 17, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Hi! First time poster, and absolute amateur at Excel 😓 I've been trying to work this out for a while but I really don't know where to begin, so apologies if this has been asked in other terms.
I need help transposing the contents of one sheet into another sheet where the columns and rows are ordered differently.

Here are sample mini-sheets for reference:

Excel Help.xlsx
ABCDEFGHIJ
1ReceiptIdDateCashierCustomerNameCustomerNumberCustomerAdEntryTypeEntryNameEntryAmount
2KM-4Mar 17, 2021 5:37 PMSales AgentLarry9226220777QC Market
3KM-4ItemFries 1kg (48 X 72)3456
4KM-4ItemFries 2kg (24 X 135)3240
5
6KM-5Mar 17, 2021 5:38PMSales AgentVicky9059642397FV Market
7KM-5ItemJv Original (48 X 308)14784
8KM-5ItemFmc Dory (24 X 99)2376
9KM-5ItemFmc Tuna (24 X 142.5)3420
10
11
Sheet1


Excel Help.xlsx
ABCDEFGHIJ
1DATESALESMANSTORE NAMELOCATIONPACK#ITEMPRICE/PACKTOTAL
2BCDFHH HI<--Sheet 1 Columns
3Mar 17, 2021 5:37PMSales AgentLarryQC Market48Fries 1kg723456<--sample filled
4Mar 17, 2021 5:37PMSales AgentLarryQC Market24Fries 2kg1353240
5Mar 17, 2021 5:38PMSales AgentVickyFV Market48Jv Original30814784
6Mar 17, 2021 5:38PMSales AgentVickyFV Market24Fmc Dory992376
7Mar 17, 2021 5:38PMSales AgentVickyFV Market24Fmc Tuna142.53420
8
Sheet2


Sheet 1 is the csv output for sales reports from a POS app.
Sheet 2 is the format that our sales team uses.
I would like the pertinent data from Sheet 1 transposed onto sheet 2 (as shown in the blue highlighted row and in the sample filled rows).
In practice, I would want any new data pasted into Sheet 1 transposed accordingly in Sheet 2.

The H Column in particular from Sheet 1 will have contents split among 3 columns in Sheet 2 (Pack#, Item, Price/Pack).

The cell contents from B to F in Sheet 1 need to be repeated for each related entry in Sheet 2 as well. The POS csv output format is fixed and leaves several empty cells.

I hope that makes sense! I've been trying to get this done with vlookup, index, and match but I've been really slow grasping how things work 😬
Any help or even a nudge in the right direction would be extremely appreciated. Thank you!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try:
VBA Code:
Sub TransposeData()
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, FR As Long, cnt As Long, i As Long, ii As Long, rng As Range
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            FR = .Areas.Item(i).Row
            cnt = .Areas.Item(i).Rows.Count
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(cnt - 1) = Range("B" & FR)
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(cnt - 1) = Range("C" & FR)
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(cnt - 1) = Range("D" & FR)
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Resize(cnt - 1) = Range("F" & FR)
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(cnt - 1).Value = Range("I" & FR + 1).Resize(cnt - 1).Value
                With CreateObject("VBScript.RegExp")
                    .Global = True
                    .Pattern = "\(([^\)]+)\)"
                    For Each rng In Range("H" & FR + 1).Resize(cnt - 1)
                        If .test(rng.Value) Then
                            For ii = 0 To .Execute(rng.Value).Count - 1
                                desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(0)
                                desWS.Cells(desWS.Rows.Count, "H").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(1)
                                desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1) = Split(rng, "(")(0)
                            Next ii
                        End If
                    Next rng
                End With
            End With
        Next i
    End With
End Sub
 

kidneythief

New Member
Joined
Mar 17, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Thank you so much, mumps! I needed to read up a bit on VBAs but that worked perfectly! Now I'm motivated to spend time understanding how this code actually works 😅
Try:
VBA Code:
Sub TransposeData()
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, FR As Long, cnt As Long, i As Long, ii As Long, rng As Range
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            FR = .Areas.Item(i).Row
            cnt = .Areas.Item(i).Rows.Count
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(cnt - 1) = Range("B" & FR)
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(cnt - 1) = Range("C" & FR)
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(cnt - 1) = Range("D" & FR)
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Resize(cnt - 1) = Range("F" & FR)
                .Cells(.Rows.Count, "I").End(xlUp).Offset(1).Resize(cnt - 1).Value = Range("I" & FR + 1).Resize(cnt - 1).Value
                With CreateObject("VBScript.RegExp")
                    .Global = True
                    .Pattern = "\(([^\)]+)\)"
                    For Each rng In Range("H" & FR + 1).Resize(cnt - 1)
                        If .test(rng.Value) Then
                            For ii = 0 To .Execute(rng.Value).Count - 1
                                desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(0)
                                desWS.Cells(desWS.Rows.Count, "H").End(xlUp).Offset(1) = Split("'" & .Execute(rng.Value)(ii).submatches(0), "X")(1)
                                desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1) = Split(rng, "(")(0)
                            Next ii
                        End If
                    Next rng
                End With
            End With
        Next i
    End With
End Sub
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,192
Office Version
  1. 365
Platform
  1. Windows
An alternative means to the end is with Power Query.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ReceiptId", type text}, {"Date", type datetime}, {"Cashier", type text}, {"CustomerName", type text}, {"CustomerNumber", Int64.Type}, {"CustomerAd", type text}, {"EntryType", type text}, {"EntryName", type text}, {"EntryAmount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ReceiptId] <> null)),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Date", "Cashier", "CustomerName", "CustomerNumber", "CustomerAd"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([EntryType] = "Item")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "EntryName", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"EntryName.1", "EntryName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"EntryName.1", type text}, {"EntryName.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"EntryName.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "EntryName.2", Splitter.SplitTextByDelimiter("X", QuoteStyle.Csv), {"EntryName.2.1", "EntryName.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"EntryName.2.1", Int64.Type}, {"EntryName.2.2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"ReceiptId", "CustomerNumber", "EntryType"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"EntryName.1", "Item"}, {"EntryName.2.1", "Pack#"}, {"EntryName.2.2", "Price/Pack"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Cashier", "CustomerName", "CustomerAd", "Pack#", "Item", "Price/Pack", "EntryAmount"})
in
    #"Reordered Columns"

Book2
ABCDEFGH
1DateCashierCustomerNameCustomerAdPack#ItemPrice/PackEntryAmount
23/17/2021 17:37Sales AgentLarryQC Market48Fries 1kg 723456
33/17/2021 17:37Sales AgentLarryQC Market24Fries 2kg 1353240
43/17/2021 17:38Sales AgentVickyFV Market48Jv Original 30814784
53/17/2021 17:38Sales AgentVickyFV Market24Fmc Dory 992376
63/17/2021 17:38Sales AgentVickyFV Market24Fmc Tuna 142.53420
Table1
 

kidneythief

New Member
Joined
Mar 17, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Ah, I hadn't even considered that to be an option! The Power Query language seems to be a little more intuitive as well. Would one consider Power Query easier to learn, and is it more stable in the long run compared to VBAs? Thank you for all the help Alan!
An alternative means to the end is with Power Query.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ReceiptId", type text}, {"Date", type datetime}, {"Cashier", type text}, {"CustomerName", type text}, {"CustomerNumber", Int64.Type}, {"CustomerAd", type text}, {"EntryType", type text}, {"EntryName", type text}, {"EntryAmount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ReceiptId] <> null)),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Date", "Cashier", "CustomerName", "CustomerNumber", "CustomerAd"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([EntryType] = "Item")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "EntryName", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"EntryName.1", "EntryName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"EntryName.1", type text}, {"EntryName.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",")","",Replacer.ReplaceText,{"EntryName.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "EntryName.2", Splitter.SplitTextByDelimiter("X", QuoteStyle.Csv), {"EntryName.2.1", "EntryName.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"EntryName.2.1", Int64.Type}, {"EntryName.2.2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"ReceiptId", "CustomerNumber", "EntryType"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"EntryName.1", "Item"}, {"EntryName.2.1", "Pack#"}, {"EntryName.2.2", "Price/Pack"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Cashier", "CustomerName", "CustomerAd", "Pack#", "Item", "Price/Pack", "EntryAmount"})
in
    #"Reordered Columns"

Book2
ABCDEFGH
1DateCashierCustomerNameCustomerAdPack#ItemPrice/PackEntryAmount
23/17/2021 17:37Sales AgentLarryQC Market48Fries 1kg 723456
33/17/2021 17:37Sales AgentLarryQC Market24Fries 2kg 1353240
43/17/2021 17:38Sales AgentVickyFV Market48Jv Original 30814784
53/17/2021 17:38Sales AgentVickyFV Market24Fmc Dory 992376
63/17/2021 17:38Sales AgentVickyFV Market24Fmc Tuna 142.53420
Table1
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,192
Office Version
  1. 365
Platform
  1. Windows
Actually, you need not learn the language to engage. You do steps and similar to recording a macro to get the Mcode. A very good primer for learning PQ is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
 

kidneythief

New Member
Joined
Mar 17, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
Actually, you need not learn the language to engage. You do steps and similar to recording a macro to get the Mcode. A very good primer for learning PQ is "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
I'm at least familiar with recording macros so that sounds much less daunting. Thank you for the tips!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,179
Messages
5,640,632
Members
417,159
Latest member
Mayozero

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
Top