How do I reorganize cells in a spreadsheet?

Question

I want to take a spreadsheet with almost a thousand rows similar in format to this,

Sheet1

Q   A1  Bool    A2  Bool    A3  Bool
Q1  Q1A1    1   Q1A2    0   Q1A3    0
Q2  Q2A1    0   Q2A2    1   Q2A3    0
Q3  Q3A1    0   Q3A2    0   Q3A3    1
Q4  Q4A1    0   Q4A2    1   Q4A3    0

and rearrange every row like this.

Sheet2

1   =Sheet1!A2
    =Sheet1!C2  =Sheet1!B2
    =Sheet1!E2  =Sheet1!D2
    =Sheet1!G2  =Sheet1!F2

But if I select A1:C4 and try to use Auto Fill it will not increment the row number. It jumps from row #2 to row #6. Resulting in his:

1   =Sheet1!A2
    =Sheet1!C2  =Sheet1!B2
    =Sheet1!E2  =Sheet1!D2
    =Sheet1!G2  =Sheet1!F2
2   =Sheet1!A6
    =Sheet1!C6  =Sheet1!B6
    =Sheet1!E6  =Sheet1!D6
    =Sheet1!G6  =Sheet1!F6

instead of this:

1   =Sheet1!A2
    =Sheet1!C2  =Sheet1!B2
    =Sheet1!E2  =Sheet1!D2
    =Sheet1!G2  =Sheet1!F2
2   =Sheet1!A3
    =Sheet1!C3  =Sheet1!B3
    =Sheet1!E3  =Sheet1!D3
    =Sheet1!G3  =Sheet1!F3

I haven't found any way to bypass this or any other way of arriving to the same result. Any suggestions?

Answer 1

You can use vlookup function in sheet 2. You mount the 7 cells of the 1st question using it and you can copy the group to the other questions.

For this, you'll need to add a column in sheet 1 (I will suppose it will be before column A) and fill it with sequence of number (1,2,3,...) (or q1, q2, q3, if you prefer). This will be your index column for vlookup.

OBS: I placed an image below that is easier to understand than this complicated step by step... :)

In sheet2, in A1, you place 1 (or q1 depending on what you used as index).

In B1: =vlookup(A1; Sheet1!$A$1:Sheet1!$H$???; 2)
where 
      Sheet1!....H??? is your main table ending on line ??? (H insead of G because of index column inserted)
      2 is the 1st column after the index column (B in this case as the index is in A)

In A3: =vlookup(A1; Sheet1!$A$1:Sheet1!$H$???; 4)
      4 is for 3rd column after index (= C2 in your example and D2 in modified sheet)

In B3: =vlookup(A1; Sheet1!$A$1:Sheet1!$H$???; 3)
      3 is for 2nd column after index (= B2 in your example / C2 in modified sheet)

This down to C4 where you'll use index as 7 (for F2 in your example / G2 in modified sheet)

To resume, the formula will be the same, only changing the index:

Cell  Index
B1      2
B2      4
C2      3
B3      6
C3      5
B4      8
C4      7

In A5, you put 2 (or q2) and you copy the lookup formulas (B1:C4) to B5:C8 to test if it works.

Resulting sheets:

(Obs: "Planilha" = "Sheet")

Sheet 1:

Sheet 2:

Sheet2 showing formulas: (you only need to enter B1 to C4 formulas - they can be pasted down)

To automatize the copy, you can copy/paste B1:C4 down to the end. For column A, you fill A1 with 1, select A1:A4 and drag down to the end. Not tested in excel but in libreoffice it increase the 1 so I think in excel it will do it too.

Answer 2

Thanks @laurent

Sheet1

    Q   A1  Bool    A2  Bool    A3  Bool
1   Q1  Q1A1    1   Q1A2    0   Q1A3    0
2   Q2  Q2A1    0   Q2A2    1   Q2A3    0
3   Q3  Q3A1    0   Q3A2    0   Q3A3    1
4   Q4  Q4A1    0   Q4A2    1   Q4A3    0
5   Q5  Q5A1    0   Q5A2    0   Q5A3    1

Sheet2

1   =VLOOKUP(A1,Sheet1!$A$1:$H$6,2)
    =VLOOKUP(A1,Sheet1!$A$1:$H$6,4) =VLOOKUP(A1,Sheet1!$A$1:$H$6,3)
    =VLOOKUP(A1,Sheet1!$A$1:$H$6,6) =VLOOKUP(A1,Sheet1!$A$1:$H$6,5)
    =VLOOKUP(A1,Sheet1!$A$1:$H$6,8) =VLOOKUP(A1,Sheet1!$A$1:$H$6,7)

This worked out perfectly! Thanks!