CODEWITHSUNDEEP
excelexcel-formulavba
D_G
D_G

Reputation: 13

Interspersing Cells From Different Columns in Excel

I have a rather simple Excel problem that I cannot seem to solve using the fill handle. I have spent two days developing complex formulas to extract and format hexadecimal test data but I'm stuck on what seems a trivial problem. It is best described with an image(I need 10 rep to post directly):

enter image description here

I have a pair of columns of 128 rows, each row across the columns contains consecutive data entries. I want to arrange the data in a single in-order column, as show above. I tried manually entering a series of functions in column D (=A1,=B1,=A2,=B2 etc.) for the first several rows and using the fill handle, but it ends up skipping large chunks of the data.

There must be a simple solution for this sort of rearrangement but I cannot work it out!

Upvotes: 1

Views: 173

Answers (3)

Anthony Bird
Anthony Bird

Reputation: 261

in vba, just increment your variables before ending the for, next loop like so:

Sub crundle()
     i = 1
     j = 1
     For i = 1 To 128

         ActiveSheet.Range("d" & i) = "=a" & j
         ActiveSheet.Range("d" & i + 1) = "=b" & j

         i = i + 1
         j = j + 1

     Next i


End Sub

Upvotes: 0

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

In D1 enter

    =INDIRECT("a"&QUOTIENT(ROW()+1,2))

then in D2 enter

    =INDIRECT("b"&QUOTIENT(ROW()+1,2))

then select D1:D2 and drag the fill handle in D2 down to twice as many rows you have in columns A:B.

Upvotes: 3

Chel
Chel

Reputation: 2623

Try =INDEX($A$1:$B$128,INT((ROW()+1)/2),ABS(MOD(ROW(),2)-2)). Put in C1 and fill down. If you are putting it somewhere else that doesn't begin at row 1, subtract (start row - 1) from each call to ROW().

Upvotes: 2

Related Questions