Forum Discussion

cwhyte1984's avatar
cwhyte1984
Copper Contributor
Jan 24, 2023

Formula to create concatenated size data for groups of products

I have the following data: Product Name Size All sizes Maxomorra Bikini Bottom Rainbow Age 9-10 Yrs   Maxomorra Bikini Bottom Rainbow Age 11-12 Yrs   Maxomorra Bikini Top Rainbow ...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jan 25, 2023

cwhyte1984 

You can try Power Query. In the attached file you can add data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh in order to update the green result table.

cwhyte1984's avatar
cwhyte1984
Copper Contributor
Jan 25, 2023

OliverScheurich hi there.  Thanks for the reply. I am trying to use basic formulae to do this as this file is generated from another excel with various pieces of product data. Ideally I would be able to just input the size data from the original manufacturer supplied file and then copy the formula down without the need for further manipulation. 
Once this file is created it is used as a .csv to upload product data to site. 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 25, 2023

    cwhyte1984 

    =IFERROR(INDEX(Tabelle2!$B$2:$B$21,SMALL(IF(Tabelle2!$A$2:$A$21=Tabelle2!$A2,ROW(Tabelle2!$A$2:$A$21)),COLUMN(A$1))-1),"")

    You are welcome. You can try this formula which works in Excel 2013. Enter the formula with ctrl+shift+enter in order to confirm it as an arrayformula. The formula is filled down and to the right in the attached samplefile.

     

     

Resources