Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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:

image of sheet1

Sheet 2:

image of sheet 2

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

image of sheet2 showing formulas

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.

share|improve this answer
    
Obs: I used ; to separate the parameters of the formula but depending on the localization of your worksheet you may have to use , (default in English I believe). –  laurent Dec 24 '12 at 0:18
    
Looks easier now with the images... if you find something different between text and picture, the picture is right! I didn't remove the text as it contains a few clarifications for understanding... or let me believe that... :) –  laurent Dec 24 '12 at 1:26
add comment
up vote 0 down vote accepted

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.