automate repetitive function?

[jimboryan]

The spreadsheet I'm working on is made up of 25 groups of four teams. For each team we track wins, losses, and total games played. From that we figure the win percentage. Each week before we print we have to resort each of the 25 divisions by win percentage. Is there any way to create a macro of something do that automatically? Maybe there is a way or a command I don't know about? A sample of our spreadsheet is below.


results v2 sample.xlsx

Thanks

[mehmetcik]

Sounds Like a Macro.

Let me look. BRB

[mehmetcik]

Ok

The simplest way to do this is to create a helper column at the end of your sheet.

Then a single sort would probably be all that you would require.

I am assuming that all your 25 divisions will be on sheet 1.

[mehmetcik]

Nope

It would Have worked but you have merged cells in the table.

So it will have to be 25 separate sorts.

[jimboryan]

Could you show me how it >would< have worked, and I will see if we can lose the merged cells ;-)

[mehmetcik]

Ok There is a button on this sheet what will run the macro Sort_Divisions.

You can also kick it off by pressing ctrl an s.

Enjoy.

[mehmetcik]

Ok in my original plan.

I had a formula which said:

Please Login or Register  to view this content.

What that did was to convert each % to a a Reverse division number + the % [ Division 1 became division 25 and division 25 became division 1 ].

So my macro said:

Please Login or Register  to view this content.

The type number = 1 allowed me to ignore all blanks spaces or text. They would just show the division number

So all I then had to do was sort A1 to BN175 in reverse order Highest to lowest.

Then Clear Column BN.


HOWEVER.

The revised macro works. So Lets stick with that.

[Richard Buttrey]

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Check out the attached Sheet 3 where I have shown a better way of capturing your data. This will make reporting it so much easier. The first step is to complete the new layout. When you've done that we can then show you how to report from it. I've shown you a simple filtering total. Row 3 on Sheet3 will show you the appropriate totals for the filters you choose.

Using this layout you'll easily be able to use Pivot Tables and for real wizzy pre-defined pretty reports you'll probably want to use Advanced Filtering to another area.

[jimboryan]

mehmetcik: When I try to open the verison you posted I get an error. THen when I click the sort button it says a macro is missing.

Richard: You are 100% correct. I had never thought about it that way, but everything I design I do by thinking of the OUTPUT first, and I need to re-think that. I will think about it a bit and post the re-worked spreadsheet sometime today or tomorrow (having computer issues at work today). Should I post it here, or in a new thread?

Thanks to both of you for all your help!

[Richard Buttrey]

Hi Jimbo,

Attaching it in this thread to your next post will be fine. And if you want to mock up any typical reports that you'd like to see we'll no doubt be able to come up with some simple solutions.