Cell joining problem

[Niniel]

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.

 

[Elkar]

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]

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

 

[Elkar]

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]

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

 

[Elkar]

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]

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.

 

[Niniel]

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

 

[Elkar]

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]

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

 

[Niniel]

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?

 

[Niniel]

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.