Solved

Excel 2010: Aggregating rows with detail

Posted on 2014-01-28
5
157 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 39

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:itjockey
itjockey 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 32

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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

932 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now