Excel: How to extract group of the same rows and their values into columns?

How can I extract the second table from the first one? I need to extract those titles that have exactly three costs (in the following example: f1 and f3). I tried using pivot tables but could not manage to generate the second table. Any advice would be appreciated!

UPDATE: I appreciate that Mike Honey offered a solution. However, since I'm not familiar with Power Query language at all, I am looking for a solution that uses no Add-ins.

There is some general resemblance between this problem and: Excel 2007 transpose/combine multiple rows into one; How to combine values from multiple rows into a single row in Excel?; and excel-2010-move-data-from-multiple-columns-rows-to-single-row. However, this problem differs in that the requirement is not just to transpose and aggregate data, but to do it only for those titles with exactly three costs.

Solution 1:

I would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to generate the "running count" needed to get the "cost1/2/3" column headings, and more M code to call the Table.Pivot function (it's not exposed in the Power Query UI).

I've built a prototype which you can view or download - its "Power Query demo - Pivot rows into columns with Running Count.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Basically my technique was to add a calculated column to get the "Cost Title" e.g. cost1/2/3. To get this I needed to write a "Running Count" function, to return an Index that resets for each group (title).

I got the outline for the "Running Count" function from this blog post - under "Year-to-Date Sales":

http://cwebbbi.wordpress.com/2013/10/18/implementing-common-calculations-in-power-query/

Then I used the Table.Pivot function to generate a column for each unique value in the destination column.

The documentation for Table.Pivot is here:

http://office.microsoft.com/en-au/excel-help/table-pivot-HA104111995.aspx?CTT=5&origin=HA104122363

Another example of using Table.Pivot is here:

http://cwebbbi.wordpress.com/2013/11/25/pivoting-data-in-power-query/

Finally I filtered out the rows with nothing for cost3.