Forum Discussion
Present values under or next to product codes
Hi all, I have a massive amount of data containing in one column the product code and in the next column the price. I have the same product code in a different row with different prices. I need to pre...
This is a potential solution to the class of problems that is only accessible to users of Excel 365. It is very much a case of work-in-progress, particularly so because, at the time of writing, Excel does not support the concept of nested arrays without complicated workarounds.
WorksheetFormula
= LET(
product,TRANSPOSE(UNIQUE(productCode)),
listϑ, MAP(product,Filterλ(productCode, price)),
array, REDUCE("",listϑ, HStackλ),
IFERROR(DROP(array,,1),"")
)
Filterλ
= LAMBDA(criteria, values,
LAMBDA(p,
LAMBDA(FILTER(values,criteria=p))
)
);
HStackλ
= LAMBDA(acc, ϑ,
HSTACK(acc, ϑ())
);Variant of that
=LET(codes, TRANSPOSE(UNIQUE(productCode)), VSTACK( codes, DROP( REDUCE("", codes, LAMBDA(a,v, IFNA( HSTACK(a, FILTER(price, productCode=v) ), "") ) ) ,,1) ) )
