Formula needed to reorganize data

Copper Contributor

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:testing-formulas-v1.jpg
Actual Excel Document: Attached

Your expertise would be greatly appreciated!
Jay

 

4 Replies

@illo-guy 

It can be done with Power Query.

See attached file.

 

@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"

 

@Sergei Baklan 

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

@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.