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.

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.

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...

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...

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) ))

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) ))

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.

Oops, sorry Elkar, I have to apologize. I have been totally misreading my data.

Yes, I do get the results you get.

What was wrong was my description of what I needed. :)

So contrary to all I said earlier, I do need the data to be presented like

1a, 2a, 3a, 4a..., 1b, 2b, 3b...

No problem. That should be an easy enough fix. Just flip the formula around:

=INDIRECT("C"&IF(MOD(ROW(),10)=0,10,MOD(ROW(),10)) )&"-"&INDIRECT("B"&ROUNDUP(ROW()/10,0))


"Niniel" wrote:

Oops, sorry Elkar, I have to apologize. I have been totally misreading my data.

Yes, I do get the results you get.

What was wrong was my description of what I needed. :)

So contrary to all I said earlier, I do need the data to be presented like

1a, 2a, 3a, 4a..., 1b, 2b, 3b...

Fantastic, that was it.
Thank you very much for you help!

One last thing - what numbers do I have to tweak when column A, or B for that
matter, contain fewer or more than 10 entries?

Ah, I think I figured that out.
It doesn't seem to matter how many entries are in column B, but to adapt to
changes in column A one just has to replace the 10s in your formula with the
actual number of items in the column.

Very cool, thanks again.

Elkar,

Your formula's got me thinking... instead of coding the number of rows in
Column A into the formula, could it be made so that it automatically uses the
number of actual entries? I guess there'd have to be a counting mechanism
somewhere...

Found a solution, sort of.
I outsourced the counting to an adjacent column (=COUNTA(A1:A100) and then
changed the numbers in your formula to point to this cell:

=INDIRECT("a"&IF(MOD(ROW(),($C$1))=0,($C$1),MOD(RO W(),($C$1))))&"-"&INDIRECT("B"&ROUNDUP(ROW()/($C$1),0))

That should be possible. See if this works:

=IF(ROW()COUNTA(B:B)*COUNTA(C:C),"",INDIRECT("C"& IF(MOD(ROW(),COUNTA(C:C))=0,COUNTA(C:C),MOD(ROW(), COUNTA(C:C))))&"-"&INDIRECT("B"&ROUNDUP(ROW()/COUNTA(C:C),0)))

HTH,
Elkar


"Niniel" wrote:

Elkar,

Your formula's got me thinking... instead of coding the number of rows in
Column A into the formula, could it be made so that it automatically uses the
number of actual entries? I guess there'd have to be a counting mechanism
somewhere...

Yes, that worked very nicely as well, thank you.

Why do you have the IF statement in there though?

Also, can this be made to work so that row 2 is the starting row? Then I can
have column headers.

=IF(ROW()COUNTA(B:B)*COUNTA(A:A),"",INDIRECT("A"& IF(MOD(ROW(),COUNTA(A:A))=0,COUNTA(A:A),MOD(ROW(), COUNTA(A:A))))&"-"&INDIRECT("B"&ROUNDUP(ROW()/COUNTA(A:A),0)))