Forum Discussion
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 | Age 2-3 Yrs | |
| Maxomorra Bikini Top Rainbow | Age 3-4 Yrs | |
| Maxomorra Body Long Sleeve Solid Blue Sky | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 0-3 Mths | |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 3-6 Mths | |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Solid Yellow Sun | Age 3-6 Mths | |
| Maxomorra Body Long Sleeve Solid Yellow Sun | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Fairy | Age 3-6 Mths | |
| Maxomorra Body Long Sleeve Tales Fairy | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Unicorn | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Unicorn | Age 12-24 Mths | |
| Maxomorra Body Short Sleeve Solid Blue Sky | Age 0-3 Mths | |
| Maxomorra Body Short Sleeve Solid Blue Sky | Age 3-6 Mths |
I need a formula to create a list of sizes for all of the products available in that style in column 'All Sizes' . I have spent some time with ChatGPT working on it but i continually get circular reference errors. As an example of the data required here is what it should look like:
| Product Name | Size | All sizes |
| Maxomorra Bikini Bottom Rainbow | Age 9-10 Yrs | Age 9-10 Yrs,Age 11-12 Yrs |
| Maxomorra Bikini Bottom Rainbow | Age 11-12 Yrs | Age 9-10 Yrs,Age 11-12 Yrs |
| Maxomorra Bikini Top Rainbow | Age 2-3 Yrs | Age 2-3 Yrs, Age 3-4 Yrs |
| Maxomorra Bikini Top Rainbow | Age 3-4 Yrs | Age 2-3 Yrs, Age 3-4 Yrs |
| Maxomorra Body Long Sleeve Solid Blue Sky | Age 6-12 Mths | Age 6-12 Mths |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 0-3 Mths | Age 0-3 Mths, Age 3-6 Mths, Age 6-12 Mths |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 3-6 Mths | Age 0-3 Mths, Age 3-6 Mths, Age 6-12 Mths |
| Maxomorra Body Long Sleeve Solid Pink Blossom | Age 6-12 Mths | Age 0-3 Mths, Age 3-6 Mths, Age 6-12 Mths |
| Maxomorra Body Long Sleeve Solid Yellow Sun | Age 3-6 Mths | |
| Maxomorra Body Long Sleeve Solid Yellow Sun | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Fairy | Age 3-6 Mths | |
| Maxomorra Body Long Sleeve Tales Fairy | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Unicorn | Age 6-12 Mths | |
| Maxomorra Body Long Sleeve Tales Unicorn | Age 12-24 Mths | |
| Maxomorra Body Short Sleeve Solid Blue Sky | Age 0-3 Mths | |
| Maxomorra Body Short Sleeve Solid Blue Sky | Age 3-6 Mths |
And so on. There are 1000's of lines of products thus the need to do this.
Here is the example of the ChatGPT code which isnt far away from what I think i require.
=IF(A1<>A2,B1,IF(COUNTIFS(A:A,A1,D:D,"")=0,B1,IF(COUNTIF(A:A,A1)>1,INDEX(D:D,MATCH(A1,A:A,0))&","&B1,B1)))
Help greatly appreciated.
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.
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.=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.
