How to combine duplicate id, concatenate with comma.

[eienkisu]

how do i write a vba code to convert from raw data on the left to consolidtaed inventory id on the right?

i already have a vba to copy raw data to my billing worksheet, but i dont't know how to concatenate the inventory id.

[#2]

@eienkisu I would recommend to use PowerQuery. Since your example raw data contained no information on quantities and prices, I could not generate the last three columns, though.

Please see attached.

[#3]

You can use this code:

Option Explicit

Sub Consolidate_Table()

Dim SearchTable As Range
Dim Cell As Range
Dim Dates() As Variant
Dim Pallet() As Variant
Dim rpallet As Long, rdate As Long, i As Long
Dim StorageValue As Variant
Dim r As Long

Set SearchTable = Range("RawData[Date]")

Dates = Array("1.05.2022", "2.05.2022", "3.05.2022", "4.05.2022", "5.05.2022")

Pallet = Array(5, 8, 7, 1)

r = 1

For rpallet = LBound(Pallet()) To UBound(Pallet())

For rdate = LBound(Dates()) To UBound(Dates())
StorageValue = ""

For Each Cell In SearchTable.Cells

If Cell.Value = VBA.CDate(Dates(rdate)) And _
Range("RawData[Inventory id]").Cells(Cell.Row - 3, 0).Value = Pallet(rpallet) _
Then _


StorageValue = StorageValue & ", " & Range("RawData[Inventory id]").Cells(Cell.Row - 3, 0).Offset(0, 1).Value
Debug.Print StorageValue

Sh2.Range("C" & r).Value = Right(StorageValue, Len(StorageValue) - 2)

End If


Next Cell

r = r + 1

Next rdate

Next rpallet

Sh2.Range("A1").Formula = "=INDEX(RawData[Dept],MATCH(value(LEFT(Sheet2!C1,6)),RawData[Inventory id],0),1)"

Sh2.Range("A1").AutoFill Range("A1:A" & Cells(1, 3).End(xlDown).Row)

Sh2.Range("B1").Formula = "=INDEX(RawData[pallet],MATCH(value(LEFT(Sheet2!C1,6)),RawData[Inventory id],0),1)"
Sh2.Range("B1").AutoFill Range("B1:B" & Cells(1, 3).End(xlDown).Row)
End Sub