Forum Discussion

Chariton_Xanthopoulos's avatar
Chariton_Xanthopoulos
Copper Contributor
Aug 11, 2022

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 present each product code in a different column and below it the different prices it has. Could you Help!
    • Chariton_Xanthopoulos's avatar
      Chariton_Xanthopoulos
      Copper Contributor

      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:

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        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.

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

Resources