Formula needed to reorganize data

Hello, I'm a novice Excel user, but know basic formulas, but this need is stumping me. Need to find a formula to automate 100's of product image names based on the product SKU. I have a SKU num...

testing-formulas-v1.xlsx15 KB

excel

Formulas

SergeiBaklan

Diamond Contributor

Jul 25, 2019

illo-guy

Another Power Query version, but here is with indexing of groups and after pivoting on SKU (Sheet2 attached). Loading from csv is skipped

let
    Source = Excel.CurrentWorkbook()
        {[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(
        Source,
        {"SKU", "combined SKU and GUID with .jpg"}
    ),
    #"Grouped Rows" = Table.Group(
        #"Removed Other Columns",
        {"SKU"},
        {{"Tables", each _, type table [SKU=number, combined SKU and GUID with .jpg=text, Index=number]}}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Grouped Rows",
        "Custom",
        each Table.AddIndexColumn([Tables], "Index", 1, 1)
    ),
    #"Removed Other Columns1" = Table.SelectColumns(
        #"Added Custom",{"SKU", "Custom"}
    ),
    #"Expanded Custom" = Table.ExpandTableColumn(
        #"Removed Other Columns1",
        "Custom",
        {"combined SKU and GUID with .jpg", "Index"},
        {"combined SKU and GUID with .jpg", "Index"}
    ),
    #"Pivoted Column" = Table.Pivot(
        Table.TransformColumnTypes(
            #"Expanded Custom",
            {{"SKU", type text}}, "en-GB"
        ),
        List.Distinct(
            Table.TransformColumnTypes(
                #"Expanded Custom",
                {{"SKU", type text}}, "en-GB")[SKU]
        ),
        "SKU",
        "combined SKU and GUID with .jpg"
    ),
    #"Removed Columns" = Table.RemoveColumns(
        #"Pivoted Column",
        {"Index"}
    )
in
    #"Removed Columns"

testing-formulas-v1.xlsx32 KB

illo-guy

Copper Contributor

Jul 26, 2019

SergeiBaklan

Thank you so much for your solution. From what I understand my latest version of Office for Mac desktop does not have Power Query. That would be great to have for sure.

Much appreciated!
Jay

SergeiBaklan

Diamond Contributor

Aug 14, 2019

illo-guy

Hi Jay,

If with formulas you may select first unique SKU numbers as

=IFERROR(
  INDEX($A$2:INDEX($A:$A,COUNTA($A:$A)),
    AGGREGATE(15,6,
      1/(COUNTIF($D$2:D$2,$A$2:INDEX($A:$A,COUNTA($A:$A)))=0)*
      (ROW($A$2:INDEX($A:$A,COUNTA($A:$A)))-ROW($A$1)),1
    )
  ),
"")

in E2 and drag it to the right till empty cells appear.

To pick-up GUIDs

=IFERROR(
  INDEX($C$2:INDEX($C:$C,COUNTA($C:$C)),
    AGGREGATE(15,6,
      1/($A$2:INDEX($A:$A,COUNTA($A:$A))=E$2)*
      (ROW($A$2:INDEX($A:$A,COUNTA($A:$A)))-ROW($A$1)),
      ROW()-ROW(E$2)
    )
  ),
"")

in E3, drag it down till empty cells and when entire selected range to the right.

Please see Sheet3 in attached.

Forum Discussion

Formula needed to reorganize data

Resources