Excel VBA Combine Columns based on headers

Question

I am strugggeling with creating a VBA macro, which combines the Data of Columns based on the header.

Output should be: Output (Lemon Values U I O P - wrong in the screenshot)

Also I don't have a fixed range for this data table, which gives me a headache :)

Hope someone can help me out here !

This is what I currently have:

With wb.Sheets("Sheet1").UsedRange
    For C = 2 To .Columns.Count
           P = Application.Match(.Cells(C), .Rows(1), 0)
        If P < C Then
            Range(.Cells(2, C), .Cells(C).End(xlDown)).Copy .Cells(P).End(xlDown)(2)
            S = IIf(S > "", S & ",", "") & .Cells(C).Address(0, 0)
        End If
    Next
End With
    If S > "" Then Range(S).EntireColumn.Delete

Also, if you’re struggling, does that mean you’ve made an attempt? If so, can you show us what you’ve done this far? — Skin
– Skin, Commented Jan 21, 2022 at 12:42
oh in my picture lemon is not the correct output, it should be: U I O P — prvtat
– prvtat, Commented Jan 21, 2022 at 12:50

CDP1802 · Accepted Answer · 2022-01-21 13:48:59Z

1

Use nested Dictionaries for uniqueness.

Option Explicit

Sub Macro1()

    Dim wb As Workbook, wsIn As Worksheet, wsOut As Worksheet
    Dim ar, dict As Object, k, val
    Dim i As Long, j As Long, hdr As String
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set wb = ThisWorkbook
    
    ' input data into array
    Set wsIn = wb.Sheets("Sheet1")
    ar = wsIn.UsedRange
    
    ' scan array elements into dict/collection
    For j = 1 To UBound(ar, 2)
        hdr = Trim(ar(1, j))
        For i = 2 To UBound(ar)
            If Not dict.exists(hdr) Then
                dict.Add hdr, CreateObject("Scripting.Dictionary")
            End If
            
            val = ar(i, j)
            If Len(val) > 0 Then
                 dict(hdr)(val) = 1 ' add to keys
            End If
        Next
    Next
    
    ' output
    i = 0
    j = 0
    Set wsOut = wb.Sheets("Sheet2")
    With wsOut
        For Each k In dict
            j = j + 1
            i = 1
            .Cells(i, j) = k
            For Each val In dict(k)
                i = i + 1
                .Cells(i, j) = val
            Next
        Next
     End With
 End Sub

answered Jan 21, 2022 at 13:48

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prvtat Over a year ago

oh wow thank you very much! works perfectly even with big amounts of data. :-) my biggest problem was that I couldnt delete that many rows out of the range in this line: If S > "" Then Range(S).EntireColumn.Delete

Collectives™ on Stack Overflow

Excel VBA Combine Columns based on headers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related