Home

Formula needed to reorganize data

%3CLINGO-SUB%20id%3D%22lingo-sub-773823%22%20slang%3D%22en-US%22%3EFormula%20needed%20to%20reorganize%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%222%22%3EHello%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3EI'm%20a%20novice%20Excel%20user%2C%20but%20know%20basic%20formulas%2C%20but%20this%20need%20is%20stumping%20me.%3CSPAN%3E%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3ENeed%20to%20find%20a%20formula%20to%20automate%20100's%20of%20product%20image%20names%20based%20on%20the%20product%20SKU.%20I%20have%20a%20SKU%20number%2C%20imagename.jpg%20in%202%20columns%20and%20need%20to%20have%20that%20list%20of%20individual%20SKUs%20in%20a%20new%20column%20with%20the%20corresponding%20images%20associated%20with%20that%20SKU.%20Each%20SKU%20has%20a%20different%20number%20of%20images.%20The%20image%20names%20have%20the%20SKU%20number%20in%20the%20name.%20I%20can%20Transpose%20the%20different%20SKUs%20in%20separate%20columns%20but%20I%20don't%20know%20how%20to%20set%20a%20formula%20to%20go%20to%20the%20next%20column%20once%20there%20is%20no%20more%20images%20with%20that%20SKU%20in%20the%20name.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20was%20thinking%20this%20could%20be%20done%20with%20VLOOKUP%2C%20INDEX%20and%20MATCH%20and%2For%20IF%20formulas%20as%20combined%20functions.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3EIMAGE%20of%20what%20I'm%20trying%20to%20do%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124510i2C76C3AC95BBEFF5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22testing-formulas-v1.jpg%22%20title%3D%22testing-formulas-v1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CIMG%20src%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2F43lzr9xlpz5w3cd%2Ftesting-formulas-v1.JPG%3Fdl%3D0%22%20border%3D%220%22%20alt%3D%22%22%20%2F%3EActual%20Excel%20Document%3A%20Attached%3C%2FFONT%3E%3CFONT%20color%3D%22%23212121%22%3E%3CSPAN%3E%3CFONT%20size%3D%222%22%3E%3CBR%20%2F%3EYour%20expertise%20would%20be%20greatly%20appreciated!%3CBR%20%2F%3EJay%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-773823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774113%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20needed%20to%20reorganize%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382233%22%20target%3D%22_blank%22%3E%40illo-guy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20can%20be%20done%20with%20Power%20Query.%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774248%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20needed%20to%20reorganize%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F382233%22%20target%3D%22_blank%22%3E%40illo-guy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20Power%20Query%20version%2C%20but%20here%20is%20with%20indexing%20of%20groups%20and%20after%20pivoting%20on%20SKU%20(Sheet2%20attached).%20Loading%20from%20csv%20is%20skipped%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%0A%20%20%20%20%20%20%20%20%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%7B%22SKU%22%2C%20%22combined%20SKU%20and%20GUID%20with%20.jpg%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Grouped%20Rows%22%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20%23%22Removed%20Other%20Columns%22%2C%0A%20%20%20%20%20%20%20%20%7B%22SKU%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Tables%22%2C%20each%20_%2C%20type%20table%20%5BSKU%3Dnumber%2C%20combined%20SKU%20and%20GUID%20with%20.jpg%3Dtext%2C%20Index%3Dnumber%5D%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Grouped%20Rows%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20Table.AddIndexColumn(%5BTables%5D%2C%20%22Index%22%2C%201%2C%201)%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns1%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%7B%22SKU%22%2C%20%22Custom%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Expanded%20Custom%22%20%3D%20Table.ExpandTableColumn(%0A%20%20%20%20%20%20%20%20%23%22Removed%20Other%20Columns1%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20%7B%22combined%20SKU%20and%20GUID%20with%20.jpg%22%2C%20%22Index%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22combined%20SKU%20and%20GUID%20with%20.jpg%22%2C%20%22Index%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Pivoted%20Column%22%20%3D%20Table.Pivot(%0A%20%20%20%20%20%20%20%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%20%20%20%20%23%22Expanded%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%7B%22SKU%22%2C%20type%20text%7D%7D%2C%20%22en-GB%22%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20List.Distinct(%0A%20%20%20%20%20%20%20%20%20%20%20%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23%22Expanded%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%7B%22SKU%22%2C%20type%20text%7D%7D%2C%20%22en-GB%22)%5BSKU%5D%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%22SKU%22%2C%0A%20%20%20%20%20%20%20%20%22combined%20SKU%20and%20GUID%20with%20.jpg%22%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%0A%20%20%20%20%20%20%20%20%23%22Pivoted%20Column%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Index%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Removed%20Columns%22%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
illo-guy
New 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.