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

[Laura_10]

Hi there,</SPAN>
I have to concatenate data from multiple cells in a row but grouping them based on duplicate values. </SPAN>
The sample data are in the table below between columns A and N, rows 1 to 5. </SPAN>
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:</SPAN>

A</SPAN>

B</SPAN>

C</SPAN>

D</SPAN>

E</SPAN>

F</SPAN>

G</SPAN>

H</SPAN>

I</SPAN>

J</SPAN>

K</SPAN>

L</SPAN>

M</SPAN>

N</SPAN>

Row1</SPAN>

H</SPAN>

x</SPAN>

D</SPAN>

y</SPAN>

C</SPAN>

x</SPAN>

F</SPAN>

z</SPAN>

H</SPAN>

y</SPAN>

Q</SPAN>

z</SPAN>

G</SPAN>

x</SPAN>

Row2</SPAN>

M</SPAN>

y</SPAN>

P</SPAN>

x</SPAN>

S</SPAN>

y</SPAN>

Q</SPAN>

y</SPAN>

D</SPAN>

x</SPAN>

Row3</SPAN>

J</SPAN>

v</SPAN>

E</SPAN>

v</SPAN>

W</SPAN>

v</SPAN>

Row4</SPAN>

P</SPAN>

z</SPAN>

C</SPAN>

z</SPAN>

N</SPAN>

x</SPAN>

T</SPAN>

z</SPAN>

Row5</SPAN>

E</SPAN>

x</SPAN>

O</SPAN>

x</SPAN>

<TBODY>
</TBODY>

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:</SPAN>

	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>

<TBODY>
</TBODY>

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

Could someone help me. I much appreciate any ideas.</SPAN>

Thank you,</SPAN>
Laura_10</SPAN>

 

[mirabeau]

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

Just to respond to this, here's three changes marked in red.
Try it if you like, but I don't know whether it will work or not. I usually test my codes before posting, but haven't done so here except for your post#1. Sorry about that.

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

For i = 5 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, "co") = Mid(q, 2, Len(q) - 3) 

Next i
End Sub

 

[Marcelo Branco]

Mirela,

To clean up your data take a look at the TrimALL macro

http://dmcritchie.mvps.org/excel/join.htm#trimall

M.

 

[Laura_10]

Thank you Mirabeau for response.
I made the changes to the code and is works fine.

Thank you for your help.
Mirela

 

[Laura_10]

Hi Marcelo,

I tried the cleaning just using normal functions in Excel and I was able to clean the resulting column and to get the proper results.
Thank you for the URL. I will have a look at this macro and let you know how is working.... I am curious how can be done differently...


Thank you,
Mirela

 

[Laura_10]

Hi Marcelo,

I tried to use TrimALL macro but I can't see any difference. I do something wrong...
I copied the macro code and paste in my VB Editor. I selected the column with the results of the concatenation (the column with formula ConcatCateg(BU:CN)) and ran the macro.
I don't see any difference in the selected column...

Thank you ,
Mirela

 

[Laura_10]

Hi Marcelo,

I would like to add a question to my original posted question.
Is it possible the concatenated results to look like:

Column O

Row1 HCG x, DH y, FQz
Row2MSQ y, PD x
Row3 JEW v

i.e the grouping data (x, y, z, v,....) to be written in Italic format?


Thank you for help ,
Mirela

 

[Marcelo Branco]

Hi Marcelo,

I tried to use TrimALL macro but I can't see any difference. I do something wrong...
I copied the macro code and paste in my VB Editor. I selected the column with the results of the concatenation (the column with formula ConcatCateg(BU:CN)) and ran the macro.
I don't see any difference in the selected column...

Thank you ,
Mirela

Mirela,

You have to select the data range, not the column with the results. The extraneous characters (spaces or something else) are in your data.
Try this in a copy of your workbook.

M.

 

[Laura_10]

Hi Mirabeau,

I would like to add a question to my original posted question.
Is it possible the concatenated results to look like:

Column O

Row1 HCG x, DH y, FQ z
Row2 MSQ y, PD x
Row3 JEW v

i.e the grouping data (x, y, z, v,....) to be written in Italic format?


Thank you for help ,
Mirela​

 

[Marcelo Branco]

Hi Marcelo,

I would like to add a question to my original posted question.
Is it possible the concatenated results to look like:

Column O

Row1 HCG x, DH y, FQz
Row2MSQ y, PD x
Row3 JEW v

i.e the grouping data (x, y, z, v,....) to be written in Italic format?


Thank you for help ,
Mirela

hmm...hard job using my function, because it uses the categories (x, y, z...) as keys of a dictionary.
I dont know if it's easier with mirabeau's macro.

M.

 

[Laura_10]

Thank you Marcelo for your help and time spend with my matters.

Mirela