Sort by subtotal, make the subtotal stand out

[CC_mfc]

Hi Guys,

I'm not sure if there is any way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.

What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?

thanks in advance.

I have appreciated all the help i have got already from this board!

CC

EDIT: ok i have managed to get only the subtotal'd figures and names to be on the sheet by collapsing the figures at the top left hand side, but is there anyway that i can copy only the subtotals to another sheet as i would find this alot easier to work with to find names etc as it still searches through all the cells that aren't showing.

[carsto]

it would be easier to answer this if you post your spreadsheet,
or at least a condensed version of it!

[CC_mfc]

hi,

I have 'hopefully' added the attachemnt, this is just a mock up of the info i use.

Basicaly sheet 1 is what i start off with, sheet two is when i sort it by order column and then make add the subtotals to subtotal after each order group the total cost and visits, the other two columns are not that important.

sheet three is when i press the number two button at the top left and it collapses all the info and leaves me with subtotals. what i need to do, if possible, is "lift" the information off of sheet three so i am just left with the subtotals so that i can work with these.

sheet four is what i get when i try to copy the subtotals it ends up copying the info in between the subtotals. i only need the subtotal data to work with. one of the main problems is because the actual sheets i work with are thousands of rows long and it makes if difficult to use the find function as it still searches through the cells that have been collapsed during the subtotaling.

I have tried everything i can think of here.

any help would be greatly appreciated
thanks,
CC

[CC_mfc]

Hi Sorry i can't ge tthe attachment to upload, it's cool i'll just leave it. thanks anyway.

[VBA Noob]

Hi,

Click on the below link. There you will find links to sites for unloading files. Then just post back with the link

VBA Noob

[CC_mfc]

http://www.savefile.com/files/477809

ok, hopefully this will give you an idea of what i mean.

[carsto]

If this is a one-time thing here is a simple solution.
(However, if the unique items in column A are ever changing and you want Live Data, you’ll want a more sophisticated approach.)

Use Data > Filter > Advanced Filter and copy the Unique Items to a new location.
The new location has to be on the same sheet but once you’ve created the list, cut & paste it to another sheet into Column A. In cell A2 you will have “abc”.

Then use this formula in B2 to create your subtotals
=SUMIF('Sheet 1'!Criteria,A2,'Sheet 1'!D:D)

If you didn’t have column A named “Criteria”, you’re formula would be
=SUMIF('Sheet 1'!A:A,A2,'Sheet 1'!D:D)

B2 will return 80.5

[CC_mfc]

sorry, i have tried what ou suggestted but i don't know which info to start with.

if it's the 'raw' info that i start with ie sheet 1 thenwhen i try the advance filter all i get is the same list duplicated.

basically i want a function that is similar to copy and paste values only when using sheet 3. i've tried using auto filter but that just makes all the values for cost and visits 0.

i'm thinking maybe when i'm at sheet 3 isn't there a way i can remove the formula and keep the values and i could then filter them and copy only the subtotals. what i want to end up with is just the subtotal order name, subtotal'd amount of cost and clicks so i can then work with this info.

thanks

[carsto]

Start on Sheet 1, with your raw data
You don't need all the outlined data on those other sheets
Under Data > Filter > Advanced Filter

under Action select
Copy to another Location

List Range:
$A:$A
you can just select column A with your mouse

Criteria Range:
$A:$A
you can just select column A with your mouse

Copy to:
'Sheet 1'!$G$1
you can just select cell G1 with your mouse

CHECK
Unique Records only

then OK

then cut & paste column G to another sheet & apply the formula above.

[CC_mfc]

Hi,

Thanks that works great!

i see what you mean that it would be ok for a one off. i have to do this many times a day with all different reports, if anyone could suggest a macro or template that could help make this a bit more quick and smooth i would really appreciate it.

thanks again!

[carsto]

first, the disclaimer: I'M NO PROGRAMMER!

All I did was record a macro and make a few minor changes

Here's your code.
The one obvious opportunity for improvement would be, I put the formula all the way down Column B. This takes up some memory.
Somebody here knows a way (I think with the xlUp command) to find the last A cell with data and only paste in B if there's data in A

Meanwhile, paste this code into a module

Please Login or Register  to view this content.

[Paul]

Here is another solution, from PCMagazine just last month:

http://www.pcmag.com/article2/0,1895,2078810,00.asp

Maybe you should subscribe, might have saved you a few days.

[CC_mfc]

Thanks for all your replies.

As suggested i have subscribed to the mag mentioned.

The Select Visible Cells comand is invaluable to me now. thanks.