Formula in Alternate rows that do not reference alternate

[mShah29]

Hello everyone,

How can I achieve this?

I need to do this for over 10,000 cells.

If I drag and drop, it picks up every alternate A and B cells.

Any suggestions are greatly appreciated.

	A	B	C	D
1			=IF(MOD(ROW(),2)=0,(IF(A1="","",A1)),(IF(B1="","",B1)))
2			=IF(MOD(ROW(),2)=0,(IF(A1="","",A1)),(IF(B1="","",B1)))
3			=IF(MOD(ROW(),2)=0,(IF(A2="","",A2)),(IF(B2="","",B2)))
4			=IF(MOD(ROW(),2)=0,(IF(A2="","",A2)),(IF(B2="","",B2)))
5			=IF(MOD(ROW(),2)=0,(IF(A3="","",A3)),(IF(B3="","",B3)))
6			=IF(MOD(ROW(),2)=0,(IF(A3="","",A3)),(IF(B3="","",B3)))

<tbody>
</tbody>

Thank you.

 

[Dad_x6]

You should be able to select all of the 6 rows you have, and then copy it down. It should grab the sequence you are doing (1, 1, 2, 2, 3, 3... and then it will automatically go to 4, 4, 5, 5, 6, 6, etc.)

I tested something very similar, and it worked.

 

[mShah29]

Thank you.

I tried... It skips 4,4,5,5,6,6 and goes from 1,1,2,2,3,3 to 7,7,8,8,9,9 then 13,13,14,14,15,15...

Other ideas?

 

[Eric W]

Nice idea, Dad_x6. I learned something new about Excel.

MShah29, Welcome to the Board!

You can also simplify the formula a bit. Put this formula in C1 and copy down:

=OFFSET($A$1,(ROW()-1)/2,MOD(ROW(),2))

 

[Jonmo1]

Are you trying to turn this

a b
c d
e f

Into this

a
b
c
d
e
f

?

Here's a start, but it doesn't handle the blanks yet, they are returned as 0's.

=INDEX(A:B,CEILING(ROW()/2,1),MOD(ROW()-1,2)+1)

The blanks can be accommodated, but more info is needed.
What type of data is in the cells, text or number, or could be either?
Which version of Excel are you using?

I mean you could Brute Force it like this
=IF(INDEX(A:B,CEILING(ROW()/2,1),MOD(ROW()-1,2)+1)="","",INDEX(A:B,CEILING(ROW()/2,1),MOD(ROW()-1,2)+1))

But there could be simpler methods depending previous questions.

 

[mShah29]

Thank you, Eric!

The formula worked, but I don't quite understand it.
What if instead of A and B, I wanted to refer column A and D, how would this change the formula?

 

[mShah29]

Thanks for the reply, Jonmo1!

No, I am trying to do this
1
1
2
2
3
3
4
4....

I am using Excel 2010

 

[Eric W]

The OFFSET function lets you pick an upper left corner, in this case $A$1, and lets you pick a row offset, and a column offset. For the row offset, I used (ROW()-1)/2. If you try this on rows 1, 2, 3, 4, 5, etc. you get 0, 0, 1, 1, 2, etc. (Note that the OFFSET function only uses the integer part, so 1.5 = 1 for its purposes.) For the column offset, I used MOD(ROW(),2) which toggles between 1 and 0 like so: 1, 2, 3, 4, 5 --> 1, 0, 1, 0, 1. So with a column offset of 1 from A1, you get B1. A column offset of 0 from A1 gets you A1.

If you want to switch between A and D, you can still use the MOD function, just multiply it by 3, so it toggles between 0 and 3.

=OFFSET($A$1,(ROW()-1)/2,MOD(ROW(),2)*3)

You can use variations of this to toggle between more columns, or to toggle between A & D instead of D & A.

This formula also has the same limitation as Jonmo's, where a blank is returned as a 0. If that's an issue, let us know and I'll figure something out.

Hope this helps!

 

[mShah29]

That is a great explanation!

Thank you, Eric. This is very helpful.

I think 0 should not be an issue.

Thanks

 

[Eric W]

Happy to help!