Interspersed Sets of Rows from Two Sheets

[michaeltsmith93]

Hi all,

I am trying to combine sets of rows from two sheets. Here's a brief illustration of the desired product:

Sheet 3
Rows 1-5 of Sheet 1
Rows 1-5 of Sheet 2
Rows 6-10 of Sheet 1
Rows 6-10 of Sheet 2
...continuing in sets of five

I'm currently using the following formula:
=IF(INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))="","",INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))).

I've just put the sheet names in B1 and B2 so that I can use this sheet as a template.

I recognize that I could use EXACT, but I'm still toying with the idea of eliminating the cells that contain 0 (which has a meaning in my data set), and by setting the logic equal to 0, I can accomplish that quite simply. With that in mind though, my first question is this: is there a way that I could edit all of the cells in the sheet with this formula syntax and change the "" to 0 in one fell swoop?
Second question... this formula works great in terms of the COLUMN(A:A) function. However, if I use ROW(1:1), I obviously end up losing every other set of 5 rows from each sheet. I'd like to avoid having to manually input the row numbers for every row since there'll end up being +440, and I can't imagine that there isn't a way to do this. I'm a novice with Excel, so the Forum Searching Tips didn't help me too much--I'm sorry if this is a repeated topic! Thanks so much in advance!

Michael

 

[My Aswer Is This]

Are you willing to use a Vba script?

And when do you stop doing this? Just keep running till the last row of Sheet1 ??

 

[My Aswer Is This]

Assuming both of my question are "Yes" then use this Vba script

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow Step 5
        Sheets(1).Rows(i).Resize(5).Copy Destination:=Sheets(3).Rows(Lastrowa)
        Lastrowa = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Sheets(2).Rows(i).Resize(5).Copy Destination:=Sheets(3).Rows(Lastrowa)
        Lastrowa = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[michaeltsmith93]

Thank you so much! I really appreciate it. This data might end up in an academic paper, so I'd love to acknowledge you by name--feel free to inbox me!
Michael

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance. Just name me "George"

Thank you so much! I really appreciate it. This data might end up in an academic paper, so I'd love to acknowledge you by name--feel free to inbox me!
Michael