Example:
Sheet1
Part no. Location Destination
1 A1 CE
2 A2 CE
3 A3 CE

Sheet2

Part no. Location Destination
1 A1 U2
2 A5 U2
4 A3 U2

Sheet3
Part no. Location Destination
1 A1 J
2 A5 J
5 A3 J

and continue...as many sheets.

End result as below
Sheet1 (after combine all)
Part no. Location Destination
1 A1 Com
2 A2 CE
2 A5 CE,J
3 A3 CE
4 A3 U2
5 A3 J

Currently what I did is to cut all data in all sheets and paste to
sheet1,arrange accordingly and delete certain rows that common using
true and false formula.
Pls help how to do it short cut in VBA.