Forum Discussion

illo-guy's avatar
illo-guy
Copper Contributor
Jul 25, 2019

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 number, imagename.jpg in 2 columns and need to have that list of individual SKUs in a new column with the corresponding images associated with that SKU. Each SKU has a different number of images. The image names have the SKU number in the name. I can Transpose the different SKUs in separate columns but I don't know how to set a formula to go to the next column once there is no more images with that SKU in the name. 

I was thinking this could be done with VLOOKUP, INDEX and MATCH and/or IF formulas as combined functions. 
IMAGE of what I'm trying to do:
Actual Excel Document: Attached
Your expertise would be greatly appreciated!
Jay

 

excel
Formulas

4 Replies

  • SergeiBaklan's avatar
    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"

     

    • illo-guy's avatar
      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's avatar
        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.

Resources