Sort by Total

[MarkT]

Greetings all,

I have a large spreadsheet that was exported out of my accounting program.
It lists all customers and all their sales for the last 24 months. The total
sales are listed below their individual sales, in the same column. Is there
any way that I could sort this list by the total sales amount? The total
sales are in bold, but no other customer data is on the same line. I need to
come up with all customers that purchased over $3k in this time period. The
number of sales vary by customer. Here is what the report looks like:

Customer name Inv. # sale #1
Inv. # sale #2
________
Total Sales

Customer name Inv. # sale #1 …..

I am using Excel 2007. Any help would be greatly appreciated.

 

[ShaneDevenshire]

Hi,

I'm not clear on what you want the output to look like - where will the
detail rows for each customer go in your final output?

I see two approaches.
1. Delete all the total lines and put the data into a pivot table.
2. Fill in the missing customer names and/or #'s and then apply the auto
filter to display only the total rows, and sort or copy and sort.

Pick one and I will give you more details, if you need. But I will need to
know more about how your data looks - show us some more lines.

 

[MarkT]

Thanks Shane,

I was not able to do the pivot table since the totals were in the same
column as the actual customer sales amounts.

I ended up just manually deleting; took longer than I wanted but it got the
job done.

Thanks for your help; I do appreciate it.

Mark

 

[Eduardo]

Hi Mark,
Do this , fill out the customer name in an adjacent column, then filter the
column where the total sales are, unselect the totals so you will see only
the rows with other data. select and delete these rows. Now you have only
customer and total sales

 

[MarkT]

Hi Eduardo, I understand what you are saying, however there are over three
thousand customers, so I wanted to find a faster way that was less manually
intensive.

Thanks!

Mark

 

[Eduardo]

Hi Mark,
you can do it with a formula, let's say you have in row 2 column 1 the
customer name, then in row 15 column C you have your totals, in column D
enter this formula
=+IF(A2="",C1,A2)

When you copy the formula down it will fill out with the customer name. What
you can do is to have the formula in row 1 which you can hide and then make a
macro to automatically copy the formula down

 

[MarkT]

I've never seen the "+" before the IF statement, what does that do?

 

[Eduardo]

Hi Mark,
is the same that =, it doesn't do anything is just to start the formula