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,

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​
Hi Mirela,

The following code refers to the setup in your opening post, but with italics as you requested.
Code:
Sub organise2()
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

With Cells(i, "o")
    If Len(q) > 0 Then .Cells = Mid(q, 2, Len(q) - 3)
    For Each e In d.keys
        .Characters(InStr(.Cells, e), 1).Font.Italic = True
    Next e
End With

Next i

End Sub
Only a bit near the end of the code has been changed.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Mirabeau,

Thank you for your answer. The macro formats Italic just the first letter in the word. Please see below an extract from the macro results :

[TABLE="width: 244"]
<TBODY>[TR]
[TD]MSc PhD Well</SPAN>[/TD]
[/TR]
[TR]
[TD]BA(Hons) MPhil Leeds</SPAN>[/TD]
[/TR]
[TR]
[TD]MA Calc, PhD Rice</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Is it possible to format Italic the whole word ( i.e Well ... Leeds ... Calc... Rice ) ?

Thankyou,
Mirela
 
Upvote 0
Hi Mirabeau,

I've got an email from MrExcel website that you replied to my thread. It was 1:40pm my time and I see we are both in in NZ time zone.
But I can't see the reply.

M
 
Upvote 0
Hi Mirabeau,

I've got an email from MrExcel website that you replied to my thread. It was 1:40pm my time and I see we are both in in NZ time zone.
But I can't see the reply.

M
Mirela,

The last post I made (before this one) was post#21. I see you have made two since then.

You haven't said if my modified code did just as you asked in your post#18. Did it or didn't it? It did when I tested it, and I haven't tried it on any other data.

I was kinda hoping I wouldn't need to look at your subsequent data, since it seems you've had to go through some explanations with Marcelo about the details of those. But post back if you really want me to.
 
Last edited:
Upvote 0
Hi Maribeau,

The modified code from post #21 did change to Italic the first letter in the word, but not the whole word which are in those cells.

I include below few lines from my real spreadsheet:
[TABLE="width: 244"]
<TBODY>[TR]
[TD]MSc PhD Well</SPAN>[/TD]
[/TR]
[TR]
[TD]BA(Hons) MPhil Leeds</SPAN>[/TD]
[/TR]
[TR]
[TD]MA Calc, PhD Rice</SPAN>[/TD]
[/TR]
[TR]
[TD]MSc Well

Is it possible to have Well instead of Well ? Or Leeds instead of Leeds?

Thank you,
Mirela[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 
Upvote 0
Hi Maribeau,

The modified code from post #21 did change to Italic the first letter in the word, but not the whole word which are in those cells.

I include below few lines from my real spreadsheet:
[TABLE="width: 244"]
<tbody>[TR]
[TD]MSc PhD Well[/TD]
[/TR]
[TR]
[TD]BA(Hons) MPhil Leeds[/TD]
[/TR]
[TR]
[TD]MA Calc, PhD Rice[/TD]
[/TR]
[TR]
[TD]MSc Well

Is it possible to have Well instead of Well ? Or Leeds instead of Leeds?

Thank you,
Mirela[/TD]
[/TR]
</tbody>[/TABLE]
OK Mirela, I think I see what your problem is.

Near the bottom of the code, change the bit as indicated in red.
Rich (BB code):
For Each e In d.keys
    .Characters(InStr(.Cells, e), Len(e)).Font.Italic = True
Next e
Does this help?
 
Upvote 0
Hi Mirabeau,

Apologise for mistake !

In my mind sounds nice in both ways :)

All is good and thank you for help.

Mirela
 
Upvote 0
Hi Mirabeau,

Apologise for mistake !

In my mind sounds nice in both ways :)

All is good and thank you for help.

Mirela
Mirela,

There were actually two of them (Mirabeau's), father and son, aka the elder and the younger.

Both French. The elder was an economist of some repute from about 1760 onward , no doubt one of those to whom Edmund Burke referred in his "Reflections on the Revolution in France" when noting that "the age of chivalry has gone, that of sophisters, economists and calculators has succeeded, and the glory of Europe is extinguished forever" (very prescient of Excel enthusiasts no doubt).

The younger Mirabeau was a general orator and troublemaker etc, during the French Revolution in 1790's. Both Mirabeaus featured in Francis Galton's book on "Hereditary Genius".

One has to call oneself something on these forums. Anything else you'd like to know? (apart from esoteric VBA codes of course).
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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