Concatenate cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,
I have a large list of item (literally thousands) in Excel. In a given row,
there is an item and fields of info (size, type, description, etc).

However the Description field is not restricted to just one row like the
other fields are. This is a problem for me. So I want to compile the data
in adjacent cells into one cell. Normally I could do this with a
"concatenate" function, but the problem is that there's no consistency to how
many adjacent cells to a given item description. For exampe:

Part Desc.
Item 1 Description 1
Item 2 Description 2
Description 2 con't
Description 2 con't
Item 3 Description 3
Item 4 Description 4
Description 4 con't
Item 5 Description 5

Maybe one way to "trap" the group of descriptions to a given item is by the
blank rows between Item numbers in the Part column? Anyways, does anyone
know how I can make a formula to do this? Thank you.

Snakeoids.
 
I would use the free add-in called ASAP Utilities, available at
www.asap-utilities to fill in all the items numbers with the one above, if
they are blank, then in C1 put something like =IF(A2=A1,B2,"") and copy
down.......in D1 put =IF(A3=a1,B3,"") and copy down, etc etc for as many
columns as you have the max number of rows of description. This will
eventually give you one row of all the descriptions for the first item in
each series.........then you can CONCATENATE at will....

hth
Vaya con Dios,
Chuck, CABGx3
 
OK, so that would identify each row as to what item it belongs to, but how
would I concatenate from here? I need a formula that would identify each
group to concatenate to a single cell for each item. Thank you.

Snakeoids.
 
=CONCATENATE(C1&", "&D1&", "&E1&", "&F1&", "&G1) etc etc etc for as many
columns as you have the IF formulas in. Note, you might want to copy and
pasteSpecial > Values on the IF formulas first to delete those formulas and
just have the data to concatenate.

hth
Vaya con Dios,
Chuck, CABGx3
 
Sorry. This is still not making sence to me. Let's look at this a different
way. I want to input a formula in column C that will give me the results as
shown below. Can you advise further please? Thank you.

A B C
1 Part Desc. Desc. Formula
2 Item 1 Description 1 Description 1
3 Item 2 Description 2 Description 2 (B3,4,5)
4 Description 2 con't
5 Description 2 con't
6 Item 3 Description 3 Description 3
7 Item 4 Description 4 Description 4 (B7,8)
8 Description 4 con't
9 Item 5 Description 5 Description 5
 
It's not just a matter of putting a simple formula in column C and getting
the results you want. This method is a process.....involving several cells
in each row, and quite difficult to explain in just words. If you would
send me your email address, I will send you a sample workbook called
"CONCATENATE_sample.xls" so you could see the value of placing each formula
in each cell, what text each returns, and kinda how the whole thing
works.............

My home addy is........croberts at tampabay period rr period
com..........make the obvious substitutions.

Vaya con Dios,
Chuck, CABGx3
 
OK, will close this thread by saying that I sent a sample file to the OP and
it explained the procedure to his satisfaction. Anyone following this thread
that wishes a copy of that sample file, just send me your email and I will be
happy to provide...........

Vaya con Dios,
Chuck, CABGx3
 
Back
Top