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,

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.

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.

 

[Laura_10]

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 :

MSc PhD Well</SPAN>

BA(Hons) MPhil Leeds</SPAN>

MA Calc, PhD Rice</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

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

Thankyou,
Mirela

 

[Laura_10]

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

 

[mirabeau]

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.

 

[Laura_10]

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:

MSc PhD Well</SPAN>

BA(Hons) MPhil Leeds</SPAN>

MA Calc, PhD Rice</SPAN>

MSc Well Is it possible to have Well instead of Well ? Or Leeds instead of Leeds? Thank you, Mirela

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[mirabeau]

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:

MSc PhD Well

BA(Hons) MPhil Leeds

MA Calc, PhD Rice

MSc Well Is it possible to have Well instead of Well ? Or Leeds instead of Leeds? Thank you, Mirela

<tbody>
</tbody>

OK Mirela, I think I see what your problem is.

Near the bottom of the code, change the bit as indicated in red.

For Each e In d.keys
    .Characters(InStr(.Cells, e), Len(e)).Font.Italic = True
Next e

Does this help?

 

[Laura_10]

Hi Maribeau,

It's perfect!!!
Thank you very much for your help.

Have a great weekend!
Mirela

 

[mirabeau]

Hi Maribeau,

It's perfect!!!
Thank you very much for your help.

Have a great weekend!
Mirela

Hi Mirela,

Actually, name is mirabeau.

But never mind that.

It's good that it all worked out for you in the end.

 

[Laura_10]

Hi Mirabeau,

Apologise for mistake !

In my mind sounds nice in both ways

All is good and thank you for help.

Mirela

 

[mirabeau]

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).