Solved

Excel 2010: Aggregating rows with detail

Posted on 2014-01-28
5
189 Views
Last Modified: 2014-02-19
I have a sheet that has multiple rows with the same product name.
It has columns that need to be aggregated.  The issue is that no matter which way I do it the remaining columns do not show up.
The sheet looks like:
A   C      0.15      1,200    500
B    B     0.16           50      10
A   C      0.15         100      100
...

I wish to get:
A   C     0.15      1,300    600
...

The problem is that every thing I have tried totals fine, but doesnt give me the "C" and the 0.15 in the above example.

Anyone have any ideas?
0
Comment
Question by:GNOVAK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 350 total points
ID: 39816816
You can do it with pivot table if first 3 columns will be row labels and other - data. Look at sample
Pivot.xls
0
 
LVL 8

Assisted Solution

by:Naresh Patel
Naresh Patel earned 75 total points
ID: 39816959
Hi GNOVAK,

or you can get this by using formulas :-

Find unique values of product & then use sumif formula.

See attached file.


Thanks
Aggregating-rows-with-detail.xlsx
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 75 total points
ID: 39817360
Are you using the SUBTOTAL function to insert summary rows?

If so, there is a way round to get what you want.

Apply the Subtotal function as normal and then use the Expand buttons or Number buttons at the top of the sheet to ensure all categories are fully expanded.

Then enable an Autofilter to the data and select the column which currently shows the Total heading and apply a filter for contains "Total". You will now have the total rows visible like you do when you collapse the rows using the grouping buttons. In the first of the rows where you now want the further detail, assuming column A and row 15, type formula:

=A14

Where 15 is first visible row and row 14 is actually hidden by the filter. Type it rather than selecting using the cursor as moving the cursor may just move up to the next visible row.

Now copy this cell and select the visible rows as a block and paste formulas or do a FIll Down. This will only populate the visible rows and won't overwrite the hidden data rows.

You can now remove the filter and use the Group buttons to collapse to subtotal rows but should still see the detail label of the row above.

Thanks
Rob H
0
 

Author Comment

by:GNOVAK
ID: 39817914
als315 - exactly what I want. Problem is I never get there.

Rob H -  I think you're giving me instructions on how to get there and it makes sense. Problem is I cant find what you mean when you say "enable an Autofilter to the data and select the column....."
I tried right clicking on the column and adding a name filter where it contains "Total", but the only total that appears is the Grand Total. It doesn't show <productname> Total  rows.
0
 
LVL 40

Accepted Solution

by:
als315 earned 350 total points
ID: 39818642
You can get expected reult with following steps:
1. Select your data and insert pivot table. You will get empty pivot table:
12. Move Col1-Col3 to Row labels:
23. Move Val1, Val2 to Values:
34. Press right mouse button on Col1-Col3 and remove Subtotal "Col..":
4
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question