Search code examples
excelvbapowerqueryunpivot

Loop through Matrix table and store in new sheets of column table

Matrix Table enter image description here

Column Table

enter image description here

how to convert matrix(not multiple column) to column table in VBA Code?

Sub columntomatrix
Dim mS As Worksheet
Dim eS As Worksheet

Set mS = ThisWorkbook.Sheets("Matrix")
Set eS = ThisWorkbook.Sheets("Price Entry Book")

Dim Matrix() As String
Dim entryPrice() As String
Dim Product As Range
Dim PriceBook As Range
Set Product = Range("Product")
Set PriceBook = Range("PriceBookName")

With mS.Range("B2")
    .Formula = "=IFERROR(INDEX(ListPrice,
    MATCH(" & .Offset(0,-1).Address(False, True) & "&" & 
    .Offset(-1, 0).Address(True, False) & ",ProductKey,0)),"" N/A  "")"


Product.Copy
'offset(0,-1) = selected cells move to left 1 column'
.Offset(0, -1).PasteSpecial

PriceBook.Copy
'offset(-1,0) = selected cells move to up 1 row'
.Offset(-1, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True

With Range(.Offset(0, 0), .Offset(Product.Rows.Count - 2, PriceBook.Rows.Count - 2))
    .FillDown
    .FillRight
End with
End with
End Sub

got to convert this formula to all VBA code.In the same function column to matrix.now i using the formula way, i wish to convert to VBA Coding

Solution

  • Here's the Powerquery solution in case you find it easier than the VBA one in the comments. (SO is detecting the instructions as code, even though they aren't)

    Make sure every column has a title>highlight your data>insert>add table
Data>from table/range
Select product Name>right click>unpivot other columns
Filter out N/A
Rename columns/arrange order
Add column>duplicate product name and price book
Merge new columns/rename
save&load

    Before/After enter image description here

    Code (can be copied into view>advanced editor. Be sure to leave source as whatever your source is)

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Name", type text}, {"China Price Book", type text}, {"US Price Book", Int64.Type}, {"UK Price Book", Int64.Type}, {"SG Price Book", Int64.Type}, {"JP Price Book", Int64.Type}, {"Standard Price book", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "N/A")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Price Book"}, {"Value", "List Price"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product Name", "List Price", "Price Book"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Product Name", "Product Name - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Price Book", "Price Book - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Product Name - Copy", "Price Book - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Product Key"}})
in
    #"Renamed Columns1"