An Excel question.....

[davmarie]

I want to make thing easier on me. I normally use the CDG in excel with our leagues parameters (the 18 team std mixed). A week before the auction, the teams submit their "keepers"; also in excel.
Is there a formula where i can search the keeper file and remiove the keepers from the CDG, so that all that remains are the available players. I usually set up the CDG by tiers and positions. I can then just bring a single 8 x 11 sheet to the draft with all the available players to bid on.
I've done this manually before and usually miss a player or two and hear cries of FOUL and Fine the B@#@$%#D.
NOt sure if this was the right forum, but looking for any help from people more proficient than me.

[NICK@HQ]

I know you can do this with VBA coding to search for strings and take actions. Not sure if there are built-in string matching and replacing functions where the strings being matched come from a spreadsheet.

[RAY@HQ]

It's a little trickier on the grid view, but if you're in one of the regular CDG outputs (pos report, full list, etc), it's easy. Put a filter on the Keeper column to show only the players with the 'Y' in that field. Delete all of those rows, then remove the filter. You're left with only the non-kept players.

[davmarie]

It's a little trickier on the grid view, but if you're in one of the regular CDG outputs (pos report, full list, etc), it's easy. Put a filter on the Keeper column to show only the players with the 'Y' in that field. Delete all of those rows, then remove the filter. You're left with only the non-kept players.

So i should just keep the CDG file as a straight excel sheet, no grid and then match it against my "keeper" file?
Is that like a VLOOKUP type thing?

[RAY@HQ]

Oh, I misunderstood. You have a separate Excel spreadsheet of keepers? I was reading this as you had your keepers set up in MACK. That's what I'm recommending anyway... if you set the keepers and values in MACK, the CDG output is going to reflect the prices and keeper status of your kept players, AND adjust your non-kept values for inflation... sounds like that's a step you're missing now.

[davmarie]

Ray,
It is not just my keepers. It is the other 17 teams keepers, which is submitted a week before the draft and then the comish sends out the Keeper file by team. I was trying to manipulate the names in the keeper file and match against the CDG file; therby eliminating the "keepers" and leaving just the available players to bring up. I usually do this manually with the CDG grid. That way I can look at the tier of players that match my posiion "holes" etc.

[RAY@HQ]

I understand it's a bit of a data input task, but MACK does support you entering keepers for your whole league.

[ANDY@HQ]

VLOOKUP is your answer -- manipulate the name columns in both spreadsheets so they match.

Just an example, YMMV: make a new column with a cell that contains something like '= a2 & ", " & b2' where a2 has the last name and b2 hold the first name (I am assuming you have no playID type of column, sounds quite unlikely). This way you will see the 'no matches very quickly because they will have some error code in the cell.

I hope that helps...

[PD@HQ]

I'm pretty sure you don't want to have a space after the comma in the middle term of that concatenated group. The HQ tables usually have player names as "Last,First, with no space after the comma.

[davmarie]

Sounds like it will work. As far as time, though; it may take time to manipulate the columns and names to match, etc. As opposed to my just doing CNTL find and going back and forth. I will try and play with last years files just to see.