Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
Hello,
I was wondering if there was a better way to do what I have to do... which is joining cells in a looping kind of way. Let me explain. I have a list of 10 items of column A. Then I have another list of 10 entries in column B [colour attributes, actually]. Now I want to create new values in column C that are comprised of a join of Row 1 of Column A with Rows 1 - 10 of Column B, then Row 2 of Column A with Rows 1 - 10 of Column B, etc. all the way down to Row 10 of column A. How can that be done? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
I'm sure there's a more ellegant sollution, but this should work:
=INDIRECT("A"&ROUNDUP(ROW()/10,0))&INDIRECT("B"&IF(MOD(ROW(),10)=0,10,MOD(ROW( ),10))) HTH, Elkar "Niniel" wrote: Hello, I was wondering if there was a better way to do what I have to do... which is joining cells in a looping kind of way. Let me explain. I have a list of 10 items of column A. Then I have another list of 10 entries in column B [colour attributes, actually]. Now I want to create new values in column C that are comprised of a join of Row 1 of Column A with Rows 1 - 10 of Column B, then Row 2 of Column A with Rows 1 - 10 of Column B, etc. all the way down to Row 10 of column A. How can that be done? Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
Thank you, Elkar.
That was pretty cool, but not quite yet what I need because it produces results that look like this: Col A: 1, 2, 3, 4, 5 Col B: a, b, c, d, e = Col C: 1a, 2a, 3a, 4a, 5a, 1b... whereas I need = Col C: 1a, 1b, 1c, 1d, 1e, 2a, 2b... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
Thats strange, it works as intended for me. I even tested it again on a
blank worksheet, copying & pasting the formula from my post, to make sure I didn't type anything wrong. My Column C looks like: 1a 1b 1c 1d ..... 2a 2b 2c 2d etc... I'm at a loss as to how you're getting your results. "Niniel" wrote: Thank you, Elkar. That was pretty cool, but not quite yet what I need because it produces results that look like this: Col A: 1, 2, 3, 4, 5 Col B: a, b, c, d, e = Col C: 1a, 2a, 3a, 4a, 5a, 1b... whereas I need = Col C: 1a, 1b, 1c, 1d, 1e, 2a, 2b... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
Hm, maybe it's because I had to change the columns? My data is in a different
column, so "column A" is actually column C. Here's the actual formula I'm using, slightly adapted from what you posted: =INDIRECT("c"&ROUNDUP(ROW()/10,0))&"-"&INDIRECT("B"&IF(MOD(ROW(),10)=0,10,MOD(ROW(),10) )) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
Nope, that shouldn't make any difference. The column referenced first should
repeat 10 times before incrementing. The column referneced second, should increment 10 times before repeating. I tested your formula as well, and again, it works correctly for me. The results look like: 1-a 1-b 1-c 1-d 1-e 1-f 1-g 1-h 1-i 1-j 2-a 2-b 2-c etc... That's not what you're getting? "Niniel" wrote: Hm, maybe it's because I had to change the columns? My data is in a different column, so "column A" is actually column C. Here's the actual formula I'm using, slightly adapted from what you posted: =INDIRECT("c"&ROUNDUP(ROW()/10,0))&"-"&INDIRECT("B"&IF(MOD(ROW(),10)=0,10,MOD(ROW(),10) )) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell joining problem
The data in my column A looks like this, and column B is just short text:
111ABC-1 111ABC-2 112DEF-1 112DEF-2 I even posted the relevant data to a new sheet so that all I had were columns A and B, but I got the same results. Maybe it's something in my settings. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Joining text in one cell | Excel Worksheet Functions | |||
Joining IF Statements | Excel Worksheet Functions | |||
joining worksheets | Excel Worksheet Functions | |||
HELP! Joining two docs! | Excel Discussion (Misc queries) | |||
Joining 2 SUMIF's ??? | Excel Discussion (Misc queries) |