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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies