Concatenate Group of Rows

[Xavier]

Hello,

I was wondering if someone could help me out with the following. I'm
trying to group mutiple rows into a single row using the concatenate
function. However, my trouble lies in the way my data is sorted as
follows:

Name Description
a z
a y
a x
b w
b v
c u
c t
c s
c r

What I want to do is put the discription into one single line so that
my data is presented as follows:

Name Description
a z y x
b w v
c u t s r

Does anyone know of a macro or formula that would help me achieve this?
If you need any further info, please let me know. Thanks!

Xavier

 

[Guest]

Hi Xavier,

The simplest way I could think of would be using a "Concatenate-If"
worksheet function. Have a try and see if this'll help you:

********
Assume your example was in cells A1:B10

1. Sort list by "Name" (and "Description", if you wish).
2. In cell C2, put the formula: "=IF(A2=A1,CONCATENATE(C1&B2),B2)"
So this will check if the current name is the same as the one above. If not,
then it just be the description for that row. If it is, then it'll
concatenate the descriptions together.
3. In cell D2, put the formula: "=LEN(C2)"
This will return the # of characters in the new concatenated descriptions
4. Copy the formulas down your list
5. If you now sort by Name and column D descending, the description you want
is the 1st one for each name. To extract this, you can do a VLOOKUP on Name.
********

Hope this helps,
SuperJas.

 

[Xavier]

Thanks SuperJas, that did the trick!

Hi Xavier,

The simplest way I could think of would be using a "Concatenate-If"
worksheet function. Have a try and see if this'll help you:

********
Assume your example was in cells A1:B10

1. Sort list by "Name" (and "Description", if you wish).
2. In cell C2, put the formula: "=IF(A2=A1,CONCATENATE(C1&B2),B2)"
So this will check if the current name is the same as the one above. If not,
then it just be the description for that row. If it is, then it'll
concatenate the descriptions together.
3. In cell D2, put the formula: "=LEN(C2)"
This will return the # of characters in the new concatenated descriptions
4. Copy the formulas down your list
5. If you now sort by Name and column D descending, the description you want
is the 1st one for each name. To extract this, you can do a VLOOKUP on Name.
********

Hope this helps,
SuperJas.