Quick Excel Help Please?

[audi321]

Hi all. I have a sheet which in column A has client names and in Column B has a commission figure.

Problem is, most clients have multiple entries e.g.

Smith £100.00
Smith £150.00
Smith £200.00
Bloggs £75.00
Bloggs £160.00

Is there a way to automatically place a total at the end of each name and then sort it by this subtotal? e.g

Smith £100.00
Smith £150.00
Smith £200.00
Smith Total £450.00
Bloggs £75.00
Bloggs £160.00
Bloggs Total £235.00

Even better if I could automatically then 'hide' the individual rows and just leave the totals?

Thanks in advance!

[Liszt]

Yes, just use the subtotal functionality under Data -> Outline

What version of Excel?

[audi321]

It's Office 2003 so quite old. Just to add there's about 4000 rows with around 200 clients so each client has around 20 payments.

[slinky]

Pivot table

[audi321]

Cheers Slinky that's just what I wanted.

I've managed to get them split and a 'total' row put in, but how would I actually get it to total up the figure? At the moment it looks like

Smith £100
Smith £100
Smith £100
Smith total

ie. I can't work out how to get the figures to automatically add up?

[slinky]

So you have a sub total but you want a grand total?

[sgrimshaw]

The "data" part of the pivot table needs to be the SUM

This guys drones on a bit, but does explain it quite well.

https://www.youtube.com/watch?v=AhWsczRyszU

[Liszt]

You do not need a pivot table.

Look here:
https://support.office.com/en-gb/article/Insert-su...

Highlight data you want to outline.
3 or 4 clicks and you are done.

[audi321]

slinky said:

So you have a sub total but you want a grand total?

I have no totals at all, just a space where they need to be, I think I've only done the pivot table and not the sum but I'm trying to work out where this goes

[Liszt]

You really don't need a pivot table!

Have a look at the link I posted.

Will show you how to do it to give this:

[audi321]

Liszt said:

You really don't need a pivot table!

Have a look at the link I posted.

Will show you how to do it to give this:

I'm not sure my version does this, as under 'Data' and 'Group and Outline' I do not have a Subtotal option? Although I must admit this does look easier than a pivot table

[Liszt]

audi321 said:

I'm not sure my version does this, as under 'Data' and 'Group and Outline' I do not have a Subtotal option? Although I must admit this does look easier than a pivot table

Think in 2003 it was data -> subtotals...

[audi321]

Ahhhh yes it was thank you. Don't suppose you know how to now filter it by the subtotal amounts?

[Liszt]

Should be as simple as:
Make sure that columns have headings
Select columns
Select subtotal in menu
At each change of Name, use function Sum, add subtotal to Commission. Select summary below data.

[audi321]

Thanks all for your help, all sorted now

[Liszt]

Super! Now go and get your version of office upgraded

[slinky]

Liszt said:

Super! Now go and get your version of office upgraded

[sgrimshaw]

Glad you got it sorted, but it's worth learning how to do pivot tables.

In the OP you wanted to sort by the total, this is very simple with pivot tables.

Using Excel 2010 I counted it to be about 8 clicks from start to finish including sorting by the totals

[troublesbrewing]

If you have any serious amount of data analysis to do by category/ies and don't want to use a database then pivot tables are the way to go. And very easy to use/change.

"Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tables--independent of the original data layout in your spreadsheet--by dragging and dropping columns to different rows, columns, or summary positions."