Jul 25 2019 10:05 AM
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
Jul 25 2019 01:19 PM
Jul 25 2019 02:42 PM
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"
Jul 26 2019 07:27 AM
@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
Aug 14 2019 04:37 AM
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.