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 num...
excel
Formulas
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