copy formalas of multiple rows

Question

I have worksheet with 100 rows (for example). I creating a new sheet to cover it to 400 rows. The original looks like:

name1, value1, name2, value2 
a, 1, b, 1
c, 1, d, 1

Want to make the new sheet looks like:

name, value, joined
a, 1, ab
b, 1, ab
c, 1, cd
d, 1, cd

The formal I'm using in the first two rows are:

=sheet1!A2, =sheet1!B2, =CONCATENATE(sheet1!A2,sheet1!C2)
=sheet1!C2, =sheet1!D2, =CONCATENATE(sheet1!A2,sheet1!C2)

Now I want to copy those formulas to all the other rows, but if I select those and drag it will only pay attention to the formula in the last row. I tried also with VBA but the debugger is worthless(saying there is an error without pointing to the line/actual error).

Edit When I mark all of the rows and drag with ALT pressed it preserved the formulas but it add +2 to the rows instead of +1. Getting closer..

Edit 2

Sort in VBA by putting break points on each line to identify the problematic line. Link to the VBA example I took - doing pretty much the same.

Answer 1

I can suggest an array formula free solution using IF, MOD, ROW, OFFSET, ROUNDDOWN functions along with the CONCATENATE you have been using.

This is possible by mapping each consecutive two rows in Sheet 2 to one in Sheet1.

Rows 2,3 in Sheet2  -> Row 2 in Sheet1 (i = 0)
Rows 4,5 in Sheet2  -> Row 3 in Sheet1 (i = 1)
...

Let's say 5th row in Sheet1 (using A5)

ROUNDDOWN((row(A5)-row(A$2))/2,0)

is equal to 1. (i=1)

You also need to know whether a row in Sheet1 is the first one or the second one. This means determining a row is even or odd if we start counting from row 2. (i.e. row 2 -> 1(odd), row 3 -> 2 (even), ... ) This is achieved by using MOD as such,

mod(row(A5)-row(A$2),2)

This returns 1 since (5-2=3 is an odd number.)

The rest is combining these in IFs for the first two columns and CONCATENATE for the third with OFFSET to reach the contents in Sheet 1. Formulas for each of the three columns are displayed below the table. Values you have given are changed to 1,2,3,4 (row major numbered sequence) in order to check whether correct cells are captured or not.