Grouping column values based on another column's data

Question

I am looking to prepare some reports using Excel to report the overall status of our project. Here is our data, pulled from our issue management tool.

PRO-10128   Comp1   FAIL
PRO-9963    Comp1   FAIL
PRO-10167   Comp1   PASS
PRO-9965    Comp1   PASS
PRO-11541   Comp2   BLOCKED
PRO-11536   Comp2   PASS
PRO-11539   Comp2   PASS
PRO-11518   Comp3   PASS
PRO-11519   Comp3   PASS

Based on the data above, I am looking to compile the following report:

Comp    P   F   Blocked
Comp1   2   2   0
Comp2   2   0   1
Comp3   2   0   

Answer 1

Pivot tables are great for this if your data is static or you remember to refresh after updates.

You can use COUNTIFS to overcome these limitations.

Assuming your data is in columns A-C, enter this formula in F2, and copy/paste to F2:H4:

=COUNTIFS($B:$B,$E2,$C:$C,F$1)

Add column and row headers as needed:

This formula matches courses in column B to courses in column E, and it matches grades in column C to grades in row 1.

It will autoupdate as you add more data. If you add new course titles to column B, add them to column E, and copy the formula down.

An advantage of pivot tables is that they can quickly point out typos in a dataset. But we can easily accomplish the same thing using this formula:

=IF(SUM(F:H)<COUNTA(B:B),"Check for typos!","")

Even though we're counting cells in column B only, this formula will detect typos in both columns B and C, because the summary table in F:H references both columns:

No typos:

Oops, spelled "BLOCKED" with a zero instead of an "Oh":

If typos are a real concern, and you have a lot of data, conditional formatting would work better to highlight them.

Use this conditional format rule for column B:

=ISNA(MATCH(B1,E:E,0))

... and this rule for column C:

=ISNA(MATCH(C1,F$1:H$1,0))

Example: Used the letter "El" instead of the number one, and used zero instead of an "Oh":

Answer 2

This is easily done in a pivot table. There should be some column headers though. For now, let's just call them c1, c2, and c3. Highlight all of your data, go to "insert" => "pivot table". Put c2 in the "row label", c3 in the "column label", and c3 in the "values". See my attached picture for an example.

Answer 3

Although, I am not completely sure of the limitations that you have, such as what type of changes can you make, there are a couple options.

You could use a pivot table. Add columns headers to you data. Convert to table (you do not have to do this, but it is standard practice)

Select any cell inside your data, press ctrl+*, press ctrl+t. A pop up will open and ask you if the selection has headers. Check this box.

Insert a pivot table, selecting the Comp Column as your rows, and the results column as the Columns and Values. Then you just need to remove the totals row and column from the pivot table. This is under the design tab of the PivotTable Tools, Grand Totals. (Using Excel 2013)

If you do not want the Full Pass and Fail Header in the pivot table, add a column to your data table and set the formula to (The reason to put it into a table)

=IF([@Test]="Fail","F",IF([@Test]="PASS","P",IF([@Test]="BLOCKED","BLOCKED","")))

To get it into the correct column order you can select the header in the pivot table and drag it to the correct position.

A little clean up, to get the exact look you want and your done.

One thing to remember with pivot tables is they do not auto update, so you will need to click refresh anytime the table changes.