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.

Community
  • 1
B Faley
  • 780
  • 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 Aug 18 '14 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' Aug 18 '14 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 Nov 06 '14 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 Nov 06 '14 at 20:46
  • @fixer1234 Since I'm not familiar with Power Query language at all, I would be more interested in answers that use no Add-ins. I am still waiting for another answer. – B Faley Nov 07 '14 at 08:49
  • You can use the VBA solution from http://superuser.com/questions/244507/excel-2007-transpose-combine-multiple-rows-into-one and then use a filter on the results to get only those with 3 costs (e.g., filter out anything not blank for cost4 and filter out blanks for cost3). – Excellll Nov 07 '14 at 16:57
  • @Meysam - you have great patience ... are you still waiting for an alternative? If so, you may have heard that Power Query will be built in to Excel 2016 - only a few more months away now. Would that assuage your scruples against Add-ins? Either way I really don't mind - it was an interesting learning opportunity for me and I am getting a ton of demand from my clients for Power Query projects these days. – Mike Honey Jun 30 '15 at 07:06
  • 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 Jun 30 '15 at 18:24

1 Answers1

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.

Mike Honey
  • 2,562