How to combine duplicate id, concatenate with comma.

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