1

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!

Screenshot showing desired result

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.

Improve this question
8
  • Have you tried using pivot tables? By the way, you will need one extra column telling excel which cost(cost1,cost2...) a particular row has.
    – tumchaaditya
    Commented Aug 18, 2014 at 20:00
  • possible duplicate of Excel 2007 transpose/combine multiple rows into one and How to combine values from multiple rows into a single row in Excel?; see also Excel 2010 Move data from multiple columns/rows to single row and maybe others.
    – G-Man Says 'Reinstate Monica'
    Commented Aug 18, 2014 at 20:05
  • Those examples are a little different from this requirement. The requirement here is not just to transpose and aggregate data, but to do it only if there are exactly three costs.
    – fixer1234
    Commented Nov 6, 2014 at 20:29
  • This question was posed three months ago and the single answer not accepted or upvoted. Do you still need a solution, or has it been overtaken by events and you no longer need it solved, or does Mike Honey's answer solve it? Please update the status so people will know whether to work on a solution for you. If the answer didn't solve the problem, clarify why, which will provide some direction.
    – fixer1234
    Commented Nov 6, 2014 at 20:46
  • 1
    @MikeHoney I am sorry Mike, I didn't mean to dismiss your answer and almost forgot about it. I downloaded your prototype and learned from it a a lot. I really appreciate your help buddy.
    – B Faley
    Commented Jun 30, 2015 at 18:24

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • I've updated my demo using the Pivot command which has been added to the current Power Query UI.
    – Mike Honey
    Commented Oct 2, 2014 at 3:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .