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

kidneythief · Mar 17, 2021

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

A

B

C

D

E

F

G

H

I

J

1

ReceiptId

Date

Cashier

CustomerName

CustomerNumber

CustomerAd

EntryType

EntryName

EntryAmount

2

KM-4

Mar 17, 2021 5:37 PM

Sales Agent

Larry

9226220777

QC Market

3

KM-4

Item

Fries 1kg (48 X 72)

3456

4

KM-4

Item

Fries 2kg (24 X 135)

3240

5

6

KM-5

Mar 17, 2021 5:38PM

Sales Agent

Vicky

9059642397

FV Market

7

KM-5

Item

Jv Original (48 X 308)

14784

8

KM-5

Item

Fmc Dory (24 X 99)

2376

9

KM-5

Item

Fmc Tuna (24 X 142.5)

3420

10

11

Sheet1

Excel Help.xlsx

A

B

C

D

E

F

G

H

I

J

1

DATE

SALESMAN

STORE NAME

LOCATION

PACK#

ITEM

PRICE/PACK

TOTAL

2

B

C

D

F

H

I

<--Sheet 1 Columns

3

Mar 17, 2021 5:37PM

Sales Agent

Larry

QC Market

48

Fries 1kg

72

3456

<--sample filled

4

Mar 17, 2021 5:37PM

Sales Agent

Larry

QC Market

24

Fries 2kg

135

3240

5

Mar 17, 2021 5:38PM

Sales Agent

Vicky

FV Market

48

Jv Original

308

14784

6

Mar 17, 2021 5:38PM

Sales Agent

Vicky

FV Market

24

Fmc Dory

99

2376

7

Mar 17, 2021 5:38PM

Sales Agent

Vicky

FV Market

24

Fmc Tuna

142.5

3420

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!

mumps · Mar 17, 2021

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 · Mar 18, 2021

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

alansidman · Mar 18, 2021

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

A

B

C

D

E

F

G

H

1

Date

Cashier

CustomerName

CustomerAd

Pack#

Item

Price/Pack

EntryAmount

2

3/17/2021 17:37

Sales Agent

Larry

QC Market

48

Fries 1kg

72

3456

3

3/17/2021 17:37

Sales Agent

Larry

QC Market

24

Fries 2kg

135

3240

4

3/17/2021 17:38

Sales Agent

Vicky

FV Market

48

Jv Original

308

14784

5

3/17/2021 17:38

Sales Agent

Vicky

FV Market

24

Fmc Dory

99

2376

6

3/17/2021 17:38

Sales Agent

Vicky

FV Market

24

Fmc Tuna

142.5

3420

Table1

kidneythief · Mar 18, 2021

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

A

B

C

D

E

F

G

H

1

Date

Cashier

CustomerName

CustomerAd

Pack#

Item

Price/Pack

EntryAmount

2

3/17/2021 17:37

Sales Agent

Larry

QC Market

48

Fries 1kg

72

3456

3

3/17/2021 17:37

Sales Agent

Larry

QC Market

24

Fries 2kg

135

3240

4

3/17/2021 17:38

Sales Agent

Vicky

FV Market

48

Jv Original

308

14784

5

3/17/2021 17:38

Sales Agent

Vicky

FV Market

24

Fmc Dory

99

2376

6

3/17/2021 17:38

Sales Agent

Vicky

FV Market

24

Fmc Tuna

142.5

3420

Table1

alansidman · Mar 18, 2021

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 · Mar 18, 2021

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.

I'm at least familiar with recording macros so that sounds much less daunting. Thank you for the tips!

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

kidneythief

New Member

Excel Facts

mumps

Well-known Member

kidneythief

New Member

alansidman

Well-known Member

kidneythief

New Member

alansidman

Well-known Member

kidneythief

New Member

Similar threads

Forum statistics

Share this page

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

kidneythief

New Member

Excel Facts

mumps

Well-known Member

kidneythief

New Member

alansidman

Well-known Member

kidneythief

New Member

alansidman

Well-known Member

kidneythief

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock