Excel - repeating cell joins for range of cells

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

 

[Ken Johnson]

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

=INDIRECT("A"& 1+INT((ROW(A1)-1)/10)) & " " & INDIRECT("B" & 1 +
MOD(ROW(A1)-1,10))
in C1 then filled down to C100

Ken Johnson

 

[Niniel]

Hello Ken!

Thanks a lot, that works beautifully!
If I understand this correctly, the number of entries in column B is a
variable that can and has to be changed depending on how many items are in
that column. Could that be automated so that instead of hard-coding 10 into
the formula, it checks it (something with COUNT I would think)?
Also, is there a way to make this work from row 2 so that I can have column
headings?

 

[Niniel]

All right, figured out the counting issue, so I'm pretty much set. Getting it
to work with headers would be the icing on the cake, but I am very happy with
it as is if that's not possible.
Thanks a lot again.

=INDIRECT("A"& 1+INT((ROW(A1)-1)/COUNTA(B:B))) & "-" & INDIRECT("B" & 1 +
MOD(ROW(A1)-1,COUNTA(B:B)))

 

[Niniel]

Oh, one other thing - right now the results are shown like this:

a1b1, a1b2, a1b3...

Any chance it can be made to look like a1b1, a2b1, a3b1...?

 

[Ken Johnson]

Oh, one other thing - right now the results are shown like this:

a1b1, a1b2, a1b3...

Any chance it can be made to look like a1b1, a2b1, a3b1...?

Hi Niniel,

Header is OK, however it does require a spot of hard coding, or, to
put it another way, I haven't been able to figure out a way of
avoiding the hard coding.
For header in row 1...

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))&"
"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))

in C2, then filled down to whatever.

Notice that the only change was two 1's have been changed to 2's, ie
"A"&1 and "B"&1 changed to "A"&2 and "B"&2 respectively.

For changing to a1b1, a1b2, a1b3... output it's just a matter of
swapping the INT and MOD parts over...

=INDIRECT("A"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))&"
"&INDIRECT("B"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

in C2, then filled down to wherever.

The only thing you need to be careful of is Excel automatically
changing your formula should you move the data in columns A and/or B.
If you have to move any of this data around use CUT/PASTE only, not
COPY/PASTE and not DRAG/DROP, otherwise Excel will fool with the
ROW(A1) part of the formula resulting in REF! errors.

Ken Johnson

 

[Ken Johnson]

Hi Niniel,

Header is OK, however it does require a spot of hard coding, or, to
put it another way, I haven't been able to figure out a way of
avoiding the hard coding.
For header in row 1...

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))&"
"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))

in C2, then filled down to whatever.

Notice that the only change was two 1's have been changed to 2's, ie
"A"&1 and "B"&1 changed to "A"&2 and "B"&2 respectively.

For changing to a1b1, a1b2, a1b3... output it's just a matter of
swapping the INT and MOD parts over...

=INDIRECT("A"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))&"
"&INDIRECT("B"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

in C2, then filled down to wherever.

The only thing you need to be careful of is Excel automatically
changing your formula should you move the data in columns A and/or B.
If you have to move any of this data around use CUT/PASTE only, not
COPY/PASTE and not DRAG/DROP, otherwise Excel will fool with the
ROW(A1) part of the formula resulting in REF! errors.

Ken Johnson

Oops!

Of course what I really meant was...

For changing to a1b1, a2b1, a3b1... output it's just a matter of
swapping the INT and MOD parts over...


Ken Johnson

 

[Niniel]

Hm, neither of those things really worked.
The formula for the different output gave me a div/0 error, and the
modification to accommodate a header row only works for one row.

 

[Ken Johnson]

Hm, neither of those things really worked.
The formula for the different output gave me a div/0 error, and the
modification to accommodate a header row only works for one row.

Hi Niniel,

I just tested again by making a new worksheet.
Heading in A1... "a"
Heading in B1... "b"
A2:A19... a1, a2, a3...a18
B2:B19... b1, b2, b3...b18
I copied the formula from the group email...
=INDIRECT("A"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))&"
"&INDIRECT("B"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))
then pasted it into C2.
The formula was split in two by the email so I had to get it all back
together in the one cell (C2).
I then filled it down to C325 and it worked perfectly.

So, you must be doing something wrong for it not to work.

Double check the formula, particularly the ROW(A1) parts.

The DIV0 error could only be caused by the (COUNTA(A:A)-1) part and
that would only be zero if you only had the heading in column A.

Keep checking. It got to work.

Ken Johnson

 

[Niniel]

All right, I'll give it another try tomorrow.

I had the column header in A1 and one entry in A2 (2 in B2), and I
copied/pasted your formula into C2... But yes, maybe I made a mistake
somewhere a long the way.

 

[Ken Johnson]

All right, I'll give it another try tomorrow.

I had the column header in A1 and one entry in A2 (2 in B2), and I
copied/pasted your formula into C2... But yes, maybe I made a mistake
somewhere a long the way.

Hi Niniel,

If you can't solve it feel free to send me a sample workbook to check
out.
You know how to get my gmail account from my group profile?

Ken Johnson

 

[Ken Johnson]

Forgot to mention...

I tried 1 in A and 2 in B and it still worked.

Ken Johnson

 

[Niniel]

Hello Ken,

Just tried it again, and it worked. But this is the reverse results formula:

=INDIRECT("A"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))&"-"&INDIRECT("B"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

The original one, which I do need as well, still isn't working in the sense
that after row 2, column c only lists what's in column B; in other words,
there's no joining.

Actually, I just got that to work as well:

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(B:B)-1)))&"-"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(B:B)-1)))

Turns out the formula you had posted had been counting the wrong column, A:

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))&"-"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))

Even with my limited expertise I should have seen that...
But all is well now, and I'd like to thank you once more for your invaluable
help. This will save me a lot of time.

Thanks.

 

[Ken Johnson]

Hello Ken,

Just tried it again, and it worked. But this is the reverse results formula:

=INDIRECT("A"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))&"-"&INDIRECT("B"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))

The original one, which I do need as well, still isn't working in the sense
that after row 2, column c only lists what's in column B; in other words,
there's no joining.

Actually, I just got that to work as well:

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(B:B)-1)))&"-"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(B:B)-1)))

Turns out the formula you had posted had been counting the wrong column, A:

=INDIRECT("A"&2+INT((ROW(A1)-1)/(COUNTA(A:A)-1)))&"-"&INDIRECT("B"&2+MOD(ROW(A1)-1,(COUNTA(A:A)-1)))

Even with my limited expertise I should have seen that...
But all is well now, and I'd like to thank you once more for your invaluable
help. This will save me a lot of time.

Thanks.

Hi Niniel,

It's nice to hear you got it working.

Thanks for the feedback.

Ken Johnson

 

[Ken Johnson]

Hi Niniel,

It's nice to hear you got it working.

Thanks for the feedback.

Ken Johnson

I forgot to correct something I said earlier.
I said...

"The only thing you need to be careful of is Excel automatically
changing your formula should you move the data in columns A and/or B.
If you have to move any of this data around use CUT/PASTE only, not
COPY/PASTE and not DRAG/DROP, otherwise Excel will fool with the
ROW(A1) part of the formula resulting in REF! errors."

It's not true that CUT/PASTE will prevent Excel from changing the
formula.
It's not the copying process that matters, it's the pasting process.

To move any of the column A and/or B data, after you have the column C
formula in place, you can CUT or COPY but when you paste you must use
PASTE SPECIAL-VALUES, and certainly don't use DRAG/DROP.

Ken Johnson

 

[Niniel]

Hello Ken,

Thanks for the updated warning.
I don't think that'll be an issue for me though because I'm only interested
in the contents of column c, and yes, I'm special pasting that. I just past
things into columns a and b, and then copy out the results from C.

As I said, it works great, thanks again.

 

[Ken Johnson]

Hello Ken,

Thanks for the updated warning.
I don't think that'll be an issue for me though because I'm only interested
in the contents of column c, and yes, I'm special pasting that. I just past
things into columns a and b, and then copy out the results from C.

As I said, it works great, thanks again.

You're welcome.

Ken Johnson