Stacy Rueda
Board Regular
- Joined
- Jun 23, 2016
- Messages
- 87
Hi guys, Thank you in advance to those who can help me. I know there's same issue with mine but I can't find the exactly the same because what i found almost on the internet is just only two columns, but in my case I have four columns. I also found code like below, but like I've said this is just applicable for two columns only.
And this is what I want in my excel: please see picture
[TABLE="width: 470"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]FROM[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P1[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P2[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P3[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mart[/TD]
[TD]P0[/TD]
[TD]Washer[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E3[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E4[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G1[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G4[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G7[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G8[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 314"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]FROM[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P1,P2,P3[/TD]
[TD]Chef[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mart[/TD]
[TD]P0[/TD]
[TD]Washer[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E3,E4[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G1,G4,G7,G8[/TD]
[TD]Preparation[/TD]
[TD="align: right"]180[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Option Explicit
Sub Main()
Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet3")
Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary")
Dim Data As Variant
Dim Index As Long
Dim Row As Integer: Row = 1
Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2
For Index = LBound(Data, 1) To UBound(Data, 1)
If Records.Exists(Data(Index, 1)) Then
Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2)
Else
Records.Add Data(Index, 1), Row
Destination.Cells(Row, 1).Value2 = Data(Index, 1)
Destination.Cells(Row, 2).Value2 = Data(Index, 2)
Row = Row + 1
End If
Next Index
And this is what I want in my excel: please see picture
[TABLE="width: 470"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]FROM[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P1[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P2[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P3[/TD]
[TD]Chef[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Mart[/TD]
[TD]P0[/TD]
[TD]Washer[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E3[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E4[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G1[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G4[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G7[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G8[/TD]
[TD]Preparation[/TD]
[TD="align: right"]45[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 314"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]FROM[/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]P1,P2,P3[/TD]
[TD]Chef[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Mart[/TD]
[TD]P0[/TD]
[TD]Washer[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]E3,E4[/TD]
[TD]Cleaner[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]G1,G4,G7,G8[/TD]
[TD]Preparation[/TD]
[TD="align: right"]180[/TD]
[/TR]
</tbody>[/TABLE]