Solved

Excel 2010: Aggregating rows with detail

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

739 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