Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Group of Rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Group of Rows
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Group of Rows
Thanks SuperJas, that did the trick!
superjas wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group rows (or hide rows) like in MS Project | Excel Worksheet Functions | |||
Concatenate 2 rows | Excel Discussion (Misc queries) | |||
Concatenate group of cells | Excel Worksheet Functions | |||
Macro to concatenate multiple rows in a column, without selecting each group manually | Excel Programming | |||
group by concatenate(field1,field2) | Excel Programming |