Present values under or next to product codes

[Chariton_Xanthopoulos]

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!

[#2]

@Chariton_Xanthopoulos 

=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.

[#3]

@OliverScheurich 

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:

[#4]

Hi @Chariton_Xanthopoulos 

With Power Query:

[#5]

@Chariton_Xanthopoulos 

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, ϑ())
  );

[#6]

@Chariton_Xanthopoulos 

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.

[#7]

@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) ), "") ) )
          ,,1)
 ) )