Sep 09 2021 08:07 PM
Hey all, this one may be a bit tricky and I cant think of the best way to get what I need.
Here is my data below
Column A = product code
Column B = length available
Column C = colour available
Lets take product code 00134 for this example.
I need this as the final result
I essentially need to see the product code and the size its available and all the colours available in that size. At the moment it is all on individual lines. Is this something that is possible? I need to be able to do this completely in excel and cant have any external programming to complete this.
Sep 09 2021 08:22 PM
SolutionSep 09 2021 08:33 PM
Sep 10 2021 09:24 AM
Excel 365 can support very different solutions.
= UNIQUE(Code\Size)
= TEXTJOIN(", ",,
FILTER(
Colour,
(Code=@distinctCode)*(Size=@distinctSize)
)
)
where the first formula returns every unique combination of Code and Size as a single array, whilst the second returns the colour lists one at a time.
Of little interest now, but something that could be relevant when Lambda functions are released for general use is,
= LET(
COMBINE, LAMBDA(c,s,TEXTJOIN(", ", , FILTER(Colour, (Code=c)*(Size=s)))),
arrays, CHOOSE({1,2},Code,Size),
distinct, UNIQUE(arrays),
distinctCode, INDEX(distinct,,1),
distinctSize, INDEX(distinct,,2),
colours, MAP(distinctCode,distinctSize, COMBINE),
CHOOSE({1,2,3}, distinctCode, distinctSize, colours) )
which will return the entire table of results as a single formula. This is serious programming within a cell!
Sep 09 2021 08:22 PM
Solution