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

K

kidneythief

New Member
Joined
Mar 17, 2021
Messages
34
Office Version
  1. 365
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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
 
Upvote 0
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 ?
mumps said:
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
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!
alansidman said:
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
Click to expand...
 
Upvote 0
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.
 
Upvote 0
alansidman said:
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.
Click to expand...
I'm at least familiar with recording macros so that sounds much less daunting. Thank you for the tips!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
573
madi1004
M
S
  • Solved
Transfer of data range (multiple cells) from one sheet to another sheet automatically at the next blank row.
Replies
3
Views
273
Alex Blakenburg
A
P
  • Question Question
Moving rows to different sheets based on drop down.
2
Replies
12
Views
976
purevega
P
H
  • Solved
Transpose data from columns to rows
Replies
7
Views
566
harzer
H
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
377
Coyotex3
C

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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