Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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 "Snakeoids" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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. "CLR" wrote: 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 "Snakeoids" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
=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 "Snakeoids" wrote: 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. "CLR" wrote: 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 "Snakeoids" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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 "CLR" wrote: =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 "Snakeoids" wrote: 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. "CLR" wrote: 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 "Snakeoids" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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 "Snakeoids" wrote in message ... 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 "CLR" wrote: =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 "Snakeoids" wrote: 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. "CLR" wrote: 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 "Snakeoids" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate cells
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 "CLR" wrote: 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 "Snakeoids" wrote in message ... 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 "CLR" wrote: =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 "Snakeoids" wrote: 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. "CLR" wrote: 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 "Snakeoids" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I concatenate two currency cells to show a price range? | Excel Worksheet Functions | |||
Combine 2 cells without using the CONCATENATE function | Excel Worksheet Functions | |||
CONCATENATE TWO DIFFERENT CELLS | Excel Worksheet Functions | |||
Concatenate cells in Pocket Excel | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |