Forum Discussion
Present values under or next to product codes
- OliverScheurichGold Contributor
=HLOOKUP(E$9,$E$3:$T$4,2,FALSE)
=VLOOKUP(E$9,$A$4:$B$19,2,FALSE)
With the data layout of the example you can try these formulas.
- Chariton_XanthopoulosCopper Contributor
Hi Thanks for your reply.
My data have a different layout
All my data are in one column. So in your example data in cells E3:T4 are, vertically, in the same column as data in cells A3:B19.
So I need to report them as you have mentioned below:
- OliverScheurichGold Contributor
Sub productandprice() Dim lngzeilemax As Long Dim i As Long Dim k As Long Dim j As Long Range("F:MMM").Clear j = 6 k = 3 lngzeilemax = Range("C" & Rows.Count).End(xlUp).Row Range("C3:D" & lngzeilemax).Sort key1:=Range("C3"), _ order1:=xlAscending, Header:=xlNo For i = 3 To lngzeilemax If Cells(i, 3).Value = Cells(i + 1, 3).Value Then Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = k + 1 Else Cells(2, j).Value = Cells(i, 3).Value Cells(k, j).Value = Cells(i, 4).Value k = 3 j = j + 1 End If Next i End Sub
An alternative could be this macro. In the attached file you can click the button in cell B2 to run the macro. In this example the product codes and prices are in columns C and D from row 3 downwards.
- LorenzoSilver Contributor
- PeterBartholomew1Silver Contributor
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) ) )