Excel 2007 : Need to create batting order depending on stats and can't have a player twice in line-up

I need it only to use a person once and if someone is leading 2 or more stat category I need it to just go to the next highest person.

I have 1 thru 11 batters 1-BA, 2-BA, 3-SLG, 4-SLG, 5-OPS, 6-OPS, 7 THRU 11-BA. The way I have Scott is in the line up 3 times it is in cell AB4 - AB 14. In yellow.

Importance: 4, 3, 2, 1, 5, 6, 7, 8, 9, 10, 11 batter order. If Scott is leading 2 categories and one is the 4 spot and the 2 spot, I would want him in the 4 spot if that is possible.

Thanks

Any one know who to do this or how to keep it from duplicating the same player.

Is this even possible?

I might have something for you later today.

Give this a look over. It basically filters your lineup by priority. SLG has top priority and sets the 3 & 4 slots. OPS is next priority and sets the 5 & 6 slots. Then BA is ranked to set the 1,2,7-11 slots.

It works great what would I do if I wanted to switch like the 7 spot to OPS instead of BA?

I figured it out Thanks works great

You're welcome, but I made some changes (and corrections) that you may find makes it easier.

In AC17: =Q11
In AD17: =IF(AC17="","",RANK(AC17,AC$17:AC$27,0)+COUNTIF(AC$17:AC17,AC17)-1)
In AE17: =IF(AD17>COUNTIF($AE$4:$AE$14,AC$16),R11,"")
In AF17: =IF(AE17="","",RANK(AE17,AE$17:AE$27,0)+COUNTIF(AE$17:AE17,AE17)-1)
In AG17: =IF(AND(AF17<>"",AF17>COUNTIF($AE$4:$AE$14,AE$16)),O11,"")
In AH17: =IF(AG17="","",RANK(AG17,$AG$17:$AG$27,0)+COUNTIF($AG$17:$AG17,AG17)-1)
In AI17: =B11
Select AC17:AI17 and drag down

so now I only have to change the AE column to change it up.

That's what I had in mind but I forgot to give the formula for the AC range (yellow area). I figure you won't play around with the 3 & 4 slots (always be 2nd & 1st SLG) or the 1 & 2 slots (always be 1st & 2nd BA). So with that in mind, this formula in AC8 and down:

=IF(AE8="BA",VLOOKUP(COUNTIF(AE$4:AE7,"BA")+1,$AH$17:$AI$27,2,0),IF(AE8="OPS",VLOOKUP(COUNTIF(AE$4:AE7,"OPS")+1,$AF$17:$AI$27,4,0),VLOOKUP(COUNTIF(AE$4:AE7,AE8)+1,$AD$17:$AI$27,6,0)))

Anybody have a better way to do it. This seem to not work the best or I don't know what I am doing. I want to be able to change column AE and then have it adjust the batter order off of the stats.

I've attached my copy of your file. The range of cells that can be changed to affect the batting order are AE8:AE14 (coloured blue). It seems to be working as you want it.

ok yours is working much better than mine, I must of messed something up. Yours works good. Is there a way to change the 1 thru 4 or can that not really be changed? not a big deal if it can't.

i cut that and it is working like your now. before I was getting duplicates and N/A's so now I am good. just wondering about the 1 - 4 spots?

I didn't think you'd want to change the 3 & 4 slots as it's almost an unwritten rule that your best slugger is 4 slot and next best is 3 slot. Changing those 2 would complicate things as they are reversed rank, whereas everything else is top down rank. The 1 & 2 slots should probably be determined by OBP but you didn't have that listed in AE in your sample file so I left it as BA. I didn't think you'd want SLG stat (which you have included in OPS) to have a bearing on the 1 & 2 slots.

that is fine.
How can I get what you did in the workbook to my workbook, if I cut it works great but all the cells refer to you workbook, if I copy it it doesn't work at all, it gets mess up somehow.

Do a Ctrl+H to find the reference to my file and replace it with blank field.
So Ctrl+H
In the Find field type (or better still paste a copied section of the formula that refers to my file)
In the Replace field leave it blank
Click on Replace All

OK thanks a bunch. I should be good.

You're welcome. Thanks for the 'star tap'.