Forum Discussion
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 pre...
=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.
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:
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 SubAn 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.