Concatenate data from multiple cells in a row but grouping them based on duplicate values

Laura_10 · Jul 15, 2012

Hi there,
I have to concatenate data from multiple cells in a row but grouping them based on duplicate values. 
The sample data are in the table below between columns A and N, rows 1 to 5. 
I have pairs of data in each row. The number of pairs could vary from 1 to 6. The pairs in row 1 are: H and x, D and y, C and x, F and z, H and y, Q and z, G and x. Data in columns A, C, E, G, I, K, M are unique in a row; data in columns B, D, F, H J, L , N could have duplicate values. Here is the sample:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

Row1

H

x

D

y

C

x

F

z

H

y

Q

z

G

x

Row2

M

y

P

x

S

y

Q

y

D

x

Row3

J

v

E

v

W

v

Row4

P

z

C

z

N

x

T

z

Row5

E

x

O

x

I would like to concatenate all the cells in a row into a single cell, grouping them based on duplicate values in columns B, D, F, H J, L, N. The results should look like data in column O below:

	Column O</SPAN>
Row1</SPAN>	H C G x, D H y, F Q z</SPAN>
Row2</SPAN>	M S Q y, P D x</SPAN>
Row3</SPAN>	J E W v</SPAN>
Row4</SPAN>	P C T z, N x</SPAN>
Row5</SPAN>	E O x</SPAN>

I need to find a solution in Excel to solve this problem.
Office 2010 and Windows 7 are on my computer.

Could someone help me. I much appreciate any ideas.

Thank you,
Laura_10

Marcelo Branco · Jul 15, 2012

Hi Laura and welcome to Mr Excel Forum.

Maybe this UDF (User Defined Function)

Alt+F11 to open the VBEditor
go to Insert > Module
Paste the code below in the right-panel

Code:

Function ConcatCategories(r As Range) As String
    Dim i As Long, dict As Object, v As Variant
    Dim strResult As String
    
    If r.Columns.Count Mod 2 <> 0 Then _
        ConcatCategories = "ERROR - Range has an odd number of columns": Exit Function
    
    If r.Columns.Rows.Count > 1 Then _
        ConcatCategories = "ERROR - Range has more than 1 row": Exit Function
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To r.Columns.Count
        If r.Cells(i) = "" Then Exit For
        If i Mod 2 = 0 Then
            If dict.exists(r.Cells(i).Value) Then
                dict.Item(r.Cells(i).Value) = dict.Item(r.Cells(i).Value) & " " & r.Cells(i - 1).Value
            Else
                dict.Add r.Cells(i).Value, r.Cells(i - 1).Value
            End If
        End If
    Next i
    
    For Each v In dict.keys
        strResult = strResult & dict.Item(v) & " " & v & ", "
    Next v
    
    ConcatCategories = Left(strResult, Len(strResult) - 2)
End Function

Back to Excel

in O1
=ConcatCategories(A1:N1)

copy down

M.

Laura_10 · Jul 15, 2012

Hi Marcelo,

Thank you very much for your prompt solution.
I applied the steps you suggested in the mock spreadsheet I submitted in my question , columns A to N and results in column O. The results look perfect.

In my real spreadsheet the pairs of data are from columns BU to CN; there are 20 columns for the 10 pairs of data in each row. I loaded the UDF and I placed in column CO the formula "=ConcatCategories(BUx:CNx)". I dragged down the formula to cover all rows.
The concatenated result in column CO looks good. The only observation is I get a comma at the end of most of the concatenated results (i.e the result is like "E O x, ").

I don't know what is different in my real spreadsheet, comparing to the mock sample.

Thank you ,
Mirela

Marcelo Branco · Jul 15, 2012

Hi Mirela,

Could you post the data in a row where the result shows an extra comma?

Adjust the columns width, select the row, copy (ctrl+C) and paste in the forum reply page.

M.

Marcelo Branco · Jul 16, 2012

Mirela,

Maybe there are cells with spaces inside (not empty cells)

Try this new version

Code:

Function ConcatCatg(r As Range) As String
    Dim i As Long, dict As Object, v As Variant
    Dim strResult As String
    
    If r.Columns.Count Mod 2 <> 0 Then _
        ConcatCatg = "ERROR - Range has an odd number of columns": Exit Function
    
    If r.Columns.Rows.Count > 1 Then _
        ConcatCatg = "ERROR - Range has more than 1 row": Exit Function
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To r.Columns.Count Step 2
        If Trim(r.Cells(i)) = "" Then Exit For
        If dict.exists(r.Cells(i).Value) Then
            dict.Item(r.Cells(i).Value) = dict.Item(r.Cells(i).Value) & " " & r.Cells(i - 1).Value
        Else
            dict.Add r.Cells(i).Value, r.Cells(i - 1).Value
        End If
    Next i
    
    For Each v In dict.keys
        strResult = strResult & dict.Item(v) & " " & v & ", "
    Next v
    
    ConcatCatg = Left(strResult, Len(strResult) - 2)
End Function

CO1
=ConcatCatg(BU1:CN1)

copy down

M.

Laura_10 · Jul 16, 2012

Hi Marcelo,

Thank you very much for your replay.
You are right. There are cells with space inside.
I replaced the UFD with the new version you send me. The resulting data looks fine, no comma at the end of the data. But there are some cases when I get an error . It looks like the error is for lines without any data in columns BU to CN, or no data in column BV. I inserted 7 lines from my real spreadsheet, column BU to CN, and column CO with the results. The second line below has no data.
I hope you can see properly data below.

Thank you for help,
Mirela
[TABLE="width: 1531"]
<TBODY>[TR]
[TD]BSc(Hons)[/TD]
[TD]UNSW[/TD]
[TD]PhD[/TD]
[TD]ANU[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BSc(Hons) UNSW, PhD ANU[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]BSc[/TD]
[TD] [/TD]
[TD]MSc[/TD]
[TD]Auck[/TD]
[TD]PhD[/TD]
[TD]Ohio[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]BA [/TD]
[TD] [/TD]
[TD]MA[/TD]
[TD] [/TD]
[TD]PhD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]Well[/TD]
[TD]PhD[/TD]
[TD]Tor[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MA Well, PhD Tor[/TD]
[/TR]
[TR]
[TD]MSc[/TD]
[TD]Well[/TD]
[TD]PhD[/TD]
[TD]Well[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MSc PhD Well[/TD]
[/TR]
[TR]
[TD]BA [/TD]
[TD]Well[/TD]
[TD]LLM[/TD]
[TD]Well[/TD]
[TD]DiplDrComp[/TD]
[TD]Stras[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]BA LLM Well, DiplDrComp Stras[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL><COL><COL><COL span=14><COL></COLGROUP>[/TABLE]

mirabeau · Jul 16, 2012

Mirela,

Just for interest, since you're being already well looked after, I had a look at your original problem in Post#1.

Here's a VBA code. I just tested it on your post#1 but have no idea how or if it would work on your subsequent data.

Code:

Sub organise()
Dim rws As Long, i As Long, j As Long
Dim q As String, d As Object, e
Const Cls = "A:N"
rws = Range(Cls).Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
Set d = CreateObject("scripting.dictionary")
a = Range(Cls).Resize(rws)

For i = 1 To rws
d.RemoveAll
q = vbNullString
For j = 2 To Range(Cls).Columns.Count Step 2
    If Len(a(i, j)) > 0 Then d(a(i, j)) = a(i, j)
Next j
For Each e In d.items
    For j = 1 To Range(Cls).Columns.Count Step 2
        If a(i, j + 1) = e Then q = q & " " & a(i, j)
    Next j
    q = q & " " & e & ", "
Next e

If Len(q) > 0 Then Cells(i, "o") = Mid(q, 2, Len(q) - 3)

Next i
End Sub

Marcelo Branco · Jul 16, 2012

Mirela,

Test Mirabeau's code. If it works without errors with your real data, great! Use it

Replying to #6
There is an easy workaround for the rows where there is no value in any cell.
We can wrap the function with an IF, like
CN01
=IF(COUNTA(BN1:CU1=0,"",ConcatCatg(BU1:CN1))
copy down

But i dont know an easy solution to the rows where there are blank cells on the 2nd (BV) or 4th(BX) or 6th(BZ)... columns and more data after these blank cells.

You have to insert some special code in these cells.

M.

Laura_10 · Jul 16, 2012

Hi Mirabeau,

Thank you for your help.
I ran the macro in my mock sample and the results look good. I copied and paste data from my real spreadsheet into the mock one and the results look good as well. In the mock spredasheet, data are between column A and N, the results are in column O.
In my real spreadsheet data are from column BU to CN, and the result of concatenation is in column CO; the lines start from line 5 down to 805. I made a small change to the macro replacing "A:N" with "BU:CN" and copied the macro in the real spreadsheet. I don't get any result in column CO after I ran the macro.
Can you please help me.
Thank you, Mirela

Laura_10 · Jul 16, 2012

Hi Marcel,

Than you for replay.
I entered the IF above and it solves the problem for rows without any value.
The main problem is coming form spaces which are in some of the cells.
Using the original UDF you sent me, I will do some test in Excel to remove the commas and spaces.
I will let you know what I get.

Thank you again for your help.
Mirela

Concatenate data from multiple cells in a row but grouping them based on duplicate values

Laura_10

New Member

Excel Facts

Marcelo Branco

MrExcel MVP

Laura_10

New Member

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

Laura_10

New Member

mirabeau

Banned user

Marcelo Branco

MrExcel MVP

Laura_10

New Member

Laura_10

New Member

Similar threads

Forum statistics

Share this page

Concatenate data from multiple cells in a row but grouping them based on duplicate values

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

Banned user

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock