group data in wksht columns for sorting purposes.

[Jennifer]

I can do this easily in access, but I can not seem to find a way in excel.
I am working on customer account report.

I have columns like below:
Customer name 30day 60days 90days 120days
I want to group all the accounts that have data in the 120day column
together, then 90days, then 60 and so on.

I have tried sorting and that does not work effectively.
1st, the sort feature only allows me to sort on 3 columns. I want to sort
on 7.
2nd, I would like to see all accounts with items in the 120day column at the
top of the page. If the amount in negative, it appears at the bottom of the
report.

Any suggestions?

 

[Jim Thomlinson]

The limit of 3 columns on sorting is annoying but fairly easy to et around.
You want to do a reverse sort. Lets say I want to sort by A, B, C, D then E.
Then all I do is to select column E then sort, D then sort, C then sort, B
then sort and A then Sort using the A->Z or Z->A buttons on the menu. By
doing this you should be able to float the oldest dates to the top...

 

[KC hotmail com>]

Have you tried a pivot table? The sorting is still a tricky deal but that's
probably where I'd start. You can also sort with a macro, but I tend to
avoid macros unless they're absolutely necessary.

Here's how you'd go about the macro-based solution.
- Apply auto filters to your entire list (I would also freeze pane your
header row).
- Use the macro recorder and assign a shortcut key combination (perhaps
Ctrl+7 for your 7-column sort)
- Use the auto filters to sort each column individually in backward order
(so column B descending, then C descending, etc. up to G). This makes G the
primary sort, then F the secondary sort, etc.
- Select the beginning of the table (A2?).
- Stop the recorder.

Then any time you hit Ctrl+7 Excel will do all that sorting for you (and you
didn't even need to know any Visual Basic to do it!).