Excel - combine multiple columns to a single column

Question

My excel sheet looks like this

      c1        c2      c3      c4

ROW1   abc      def      1       2

ROW2   abc      def      3       4

ROW3   klm      efg     11       5

ROW4   klm      efg     12      89

I want to combine the data into one single column separated by one comma for duplicate c1 entries. So excel sheet should look like this,

       c1        c2      c3      c4

ROW1   abc      def      1,3     2,4

ROW2   klm      efg     11,12    5,89

brettdj · Accepted Answer · 2012-05-16 06:38:40Z

6

This code will

Run on columns A:D on the current sheet
Group records that are common in columns A and B togther with combined column C and column D values respectively
runs case insensitive
outputs to a new sheet

enter image description here

    Sub QuickCombine()
    Dim X()
    Dim Y()
    Dim objDic As Object
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ws As Worksheet

    X = Range([a1], Cells(Rows.Count, "D").End(xlUp))
    Y = X
    Set objDic = CreateObject("scripting.dictionary")

    For lngRow = 1 To UBound(X, 1)
        If Not objDic.exists(LCase$(X(lngRow, 1) & X(lngRow, 2))) Then
            objDic.Add LCase$(X(lngRow, 1) & X(lngRow, 2)), lngRow
        Else
            Y(lngRow, 1) = vbNullString
            Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 3) = Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 3) & "," & X(lngRow, 3)
            Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 4) = Y(objDic.Item(LCase$(X(lngRow, 1) & X(lngRow, 2))), 4) & "," & X(lngRow, 4)
        End If
    Next

    Set ws = Sheets.Add

    ws.[a1].Resize(UBound(X, 1), UBound(X, 2)) = Y
    ws.Columns("A").SpecialCells(xlBlanks).EntireRow.Delete

End Sub

answered May 16, 2012 at 6:38

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

1 Comment

Siddharth Rout Over a year ago

+ 1 Nicely explained :) @Stuti: Brettdj was kind enough to give you everything on the plate. Show some efforts ;)

brettdj · Accepted Answer · 2012-11-02 02:13:03Z

0

You can do this using the excel concatenate function. Here's a link to a good tutorial

Also, to deal with the duplicates, you can have excel highlight duplicate entries so they can be easily deleted. See here

edited Nov 2, 2012 at 2:13

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered May 16, 2012 at 5:16

jbenes

711 silver badge12 bronze badges

1 Comment

Stuti Over a year ago

I have 50000 entries in my sheet. I want to achieve this by a code which search for duplicate entries in c1 and combine entries in c3 and c4 till then

Collectives™ on Stack Overflow

Excel - combine multiple columns to a single column

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related