Present values under or next to product codes

New Contributor
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 present each product code in a different column and below it the different prices it has. Could you Help!
6 Replies



With the data layout of the example you can try these formulas.

product codes and prices.JPG


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:




Hi @Chariton_Xanthopoulos 


With Power Query:



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.


= LET(
    listϑ,  MAP(product,Filterλ(productCode, price)),
    array,  REDUCE("",listϑ, HStackλ),

= LAMBDA(criteria, values, 

= LAMBDA(acc, ϑ, 
    HSTACK(acc, ϑ())


Sub productandprice()

Dim lngzeilemax As Long
Dim i As Long
Dim k As Long
Dim j As Long


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

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.

product code.JPG

@Peter Bartholomew 

Variant of that

=LET(codes, TRANSPOSE(UNIQUE(productCode)),
  VSTACK( codes,
          DROP( REDUCE("", codes,
               LAMBDA(a,v, IFNA( HSTACK(a, FILTER(price, productCode=v) ), "") ) )
 ) )