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

Laura_10

New Member
Joined
Jul 15, 2012
Messages
16
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>
 
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.
Rich (BB code):
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
 
Upvote 0
Thank you Mirabeau for response.
I made the changes to the code and is works fine.

Thank you for your help.
Mirela
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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​
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top