?
Solved

Excel 2010: Aggregating rows with detail

Posted on 2014-01-28
5
Medium Priority
?
203 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
5 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 1050 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 225 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 225 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 1050 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

580 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