Forum Discussion
Chariton_Xanthopoulos
Aug 11, 2022Copper Contributor
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...
Chariton_Xanthopoulos
Aug 12, 2022Copper Contributor
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
Aug 12, 2022Gold Contributor
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.