Solved

Excel 2010: Aggregating rows with detail

Posted on 2014-01-28
5
144 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 31

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 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

15 Experts available now in Live!

Get 1:1 Help Now