Join many columns into just one cell and sum the quantity

[Stacy Rueda]

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.

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

FROM
Employee	Location	Description	Quantity
Susan	P1	Chef	1
Susan	P2	Chef	1
Susan	P3	Chef	1
Mart	P0	Washer	2
Ed	E3	Cleaner	10
Ed	E4	Cleaner	10
Ram	G1	Preparation	45
Ram	G4	Preparation	45
Ram	G7	Preparation	45
Ram	G8	Preparation	45

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

FROM
Employee	Location	Description	Quantity
Susan	P1,P2,P3	Chef	3
Mart	P0	Washer	2
Ed	E3,E4	Cleaner	20
Ram	G1,G4,G7,G8	Preparation	180

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try this macro

Sub aTest()
    Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
    Dim vData As Variant
    
    With Source
        vData = .Range("A2:D" & .Cells(Rows.Count, "A").End(xlUp).Row)
    End With
    
    Dim dic As Object, i As Long
    Dim arrAux As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    For i = LBound(vData, 1) To UBound(vData, 1)
        If dic.exists(vData(i, 1)) Then
            arrAux = dic(vData(i, 1))
            arrAux(0) = arrAux(0) & ", " & vData(i, 2)
            arrAux(2) = arrAux(2) + vData(i, 4)
            dic(vData(i, 1)) = arrAux
        Else
            dic(vData(i, 1)) = Array(vData(i, 2), vData(i, 3), vData(i, 4))
        End If
    Next i
    
    Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet3")
    Dim vResult As Variant, vKey As Variant
    With Destination
        .Columns("A:D").ClearContents
        .Range("A1:D1") = Array("Employee", "Location", "Description", "Quantity")
        vResult = .Range("A2").Resize(dic.Count, 4)
        i = 0
        For Each vKey In dic.keys
            i = i + 1
            vResult(i, 1) = vKey
            vResult(i, 2) = dic(vKey)(0)
            vResult(i, 3) = dic(vKey)(1)
            vResult(i, 4) = dic(vKey)(2)
        Next vKey
        .Range("A2").Resize(dic.Count, 4) = vResult
        .Columns("A:D").AutoFit
    End With
End Sub

Hope this helps

M.

 

[Rick Rothstein]

I was not completely clear on which of those tables was your existing data. I assumed the first table was and that you wanted it changed to the second table. If this is the case, then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ReduceData()
  Dim LR As Long, N As Long, Blanks As Variant
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A2:A" & LR).Value = Evaluate("IF(A2:A" & LR & "=A1:A" & LR - 1 & ","""",A2:A" & LR & ")")
  Set Blanks = Range("A2:A" & LR).SpecialCells(xlBlanks)
  For N = Blanks.Areas.Count To 1 Step -1
    Blanks.Areas(N)(1).Offset(-1, 1) = Join(Application.Transpose(Blanks.Areas(N)(1).Offset(-1, 1).Resize(Blanks.Areas(N).Count + 1)), ",")
    Blanks.Areas(N)(1).Offset(-1, 3) = Application.Sum(Blanks.Areas(N)(1).Offset(-1, 3).Resize(Blanks.Areas(N).Count + 1))
    Blanks.Areas(N).EntireRow.Delete
  Next
End Sub[/td]
[/tr]
[/table]

 

[Stacy Rueda]

Thank you sir @Marcelo Blanco, it really works. you're a genius.

 

[Stacy Rueda]

Thank you sir @Rick Rothstein, thank you for your kindness and sharing your knowledge.

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[Stacy Rueda]

Hi sir @Marcelo Blanco, what if the column arranged like below? changes is the arrangement of column but the details is as is..How or what part code will i have to modify? Thanks again.

Employee

Description

Quantity

Location

<tbody>
</tbody>

 

[Marcelo Branco]

Hi sir @Marcelo Blanco, what if the column arranged like below? changes is the arrangement of column but the details is as is..How or what part code will i have to modify? Thanks again.

Employee

Description

Quantity

Location

<tbody>
</tbody>

Try this new version

Sub aTestV2()
    Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
    Dim vData As Variant
    
    With Source
        vData = .Range("A2:D" & .Cells(Rows.Count, "A").End(xlUp).Row)
    End With
    
    Dim dic As Object, i As Long
    Dim arrAux As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    For i = LBound(vData, 1) To UBound(vData, 1)
        If dic.exists(vData(i, 1)) Then
            arrAux = dic(vData(i, 1))
            arrAux(1) = arrAux(1) + vData(i, 3)
            arrAux(2) = arrAux(2) & ", " & vData(i, 4)
            dic(vData(i, 1)) = arrAux
        Else
            dic(vData(i, 1)) = Array(vData(i, 2), vData(i, 3), vData(i, 4))
        End If
    Next i
    
    Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet3")
    Dim vResult As Variant, vKey As Variant
    With Destination
        .Columns("A:D").ClearContents
        .Range("A1:D1") = Array("Employee", "Description", "Quantity", "Location")
        vResult = .Range("A2").Resize(dic.Count, 4)
        i = 0
        For Each vKey In dic.keys
            i = i + 1
            vResult(i, 1) = vKey
            vResult(i, 2) = dic(vKey)(0)
            vResult(i, 3) = dic(vKey)(1)
            vResult(i, 4) = dic(vKey)(2)
        Next vKey
        .Range("A2").Resize(dic.Count, 4) = vResult
        .Columns("A:D").AutoFit
    End With
End Sub

M.

 

[Stacy Rueda]

Wow, What a smart person you are sir @Marcelo Branco. Thank you very very very much!!!!

 

[Marcelo Branco]

You are very welcome.

M.