Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Present values under or next to product codes

Copper 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

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

product codes and prices.JPG

@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:

 

Chariton_Xanthopoulos_0-1660285742857.png

 

Hi @Chariton_Xanthopoulos 

 

With Power Query:

_Screenshot.png

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

image.png

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

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

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