Sort values in an Excel spreadsheet

Question

I've got a spreadsheet full of names and peoples' roles, like the one below:

Role Name  Change
1     A     Yes
2     A     No
5     A     N/Ap
1     B     Yes
3     B     No
2     C     Yes
4     C     No

I have to come up with a spreadsheet like the one below:

     1        2        3        4        5        6
A   Yes                     
B                       
C                       

Basically, it should retrieve the information from the first spreadsheet and be layed out clearly on the second one.

There are way too many names and roles to do it manually. VLMOVE won't work and I've tried MATCH and INDEX.

Answer 1

Alternative to @RocketDonkey (but thanks for more complete desired result!) could be to string together Role and Name (say in a column inserted between B & C in Sheet1 [because I think OP wants a separate sheet for the results]):

C2=A1&B2  copied down as required

then use a lookup in Sheet2!B2:

=IFERROR(VLOOKUP(B$1&$A2,Sheet1!$C$2:$D$8,2,FALSE),"")  

copied across and down as required.

This assumes the grid for the results (as in question) has been constructed (and that there are 7 rows with data - adjust $8 as necessary otherwise.)

Answer 2

Agree with @Melanie that if you can force your data into a structure that can be interpreted as numbers (1 being yes, 0 being false, for example), Pivot tables are far and away the easiest way (since they will display numbers as the values - not the text). *(see below)

If you want to display arbitrary text, you could try this:

=IF(
    SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8))=0,"",
    INDEX(
        $A$1:$C$8,
        SUMPRODUCT(--($A$2:$A$8=F$1),--($B$2:$B$8=$E2),ROW($A$2:$A$8)),
        3))

This checks to see if the SUMPRODUCT of the three columns totals 0 (which will happen when no combo of x/y is matched (like Name: C, Role: 5, for instance), and if so, it returns "". Otherwise, it returns the value in column Value.

---

---

*A ‘pivot table option’ would be to represent the Change as a number (eg as formula in D2 copied down). Then create a pivot table from (in the example) A1:D8, with fields as shown. Copy the pivot table to a different sheet with Paste Special/Values (though shown in F11:K15 of same sheet in example). Then in that other sheet select row starting with Name A and as far down as required, Replace -1 with No, 1 with Yes and 0 with N/Ap.