Custom viewing/sorting/filtering

[b0x]

I really couldnt get a decent title that said what I wanted, so I was just vague.

I am working with a spreadsheet that lists every single player season in major league baseball. For example, someone who played every year from 1920 to 1929 will have 10 entries in the file, one for each season.

I want view every player season from year X forward, but they had to play in year X. Basically I want to look at every player's career from year X forward, but if your career started after year X, I am not interested.

Hopefully you understand what I mean... Any ideas?

[NBVC]

It would help us if you supplied a sample zipped spreadsheet to see your setup.

And give an example of what you want as a result.

[b0x]

Ok, I added some sample data... the full file contains over 40k rows, so I cut it down some.

On the full file, I used auto-filters, and then filtered season to be 1930 <= season < 1951. The plan was to go through and manually delete any player who didnt have a 1930 season (delete every entry for that player), but I know there is a better way.

So, right now, every player season from 1930 to 1950 is listed, regardless of what year they first show up in the database. I want to only display players who have a season in 1930, and then display all of their seasons from 1930 forward. If the first season in the 1930-1950 range for a given player is NOT 1930, I don't want them to be displayed.

Did that help?

[NBVC]

See attached example:

I used a helper column to determine if players' starting year was the input year. (I created an input cell where you can enter the start year of interest).

So you enter the year in the input cell (e.g. in cell U1) and then the formula in the helper column lists TRUE's for all players who started in that year.

Then you filter that column (Column S) by TRUE....

Formula used in S2:

=MIN(IF($B$2:$B$603&$C$2:$C$603=B6&C6,$A$2:$A$603))=$U$1

which is confirmed with CTRL+SHIFT+ENTER not just ENTER. It is an array formula.

[b0x]

Very cool man, thanks. That badboy takes some time to do it's thing on 40k records though

Now, will that only work if I have the min year filtered beforehand?

[NBVC]

Very cool man, thanks. That badboy takes some time to do it's thing on 40k records though

Unfortunately, that's a drawback of most array formulas...the more records to scan the longer it takes... Not sure if it will help much but you can create another helper column that concatenates first and last names, then you can base the array formula on that column and see how it works.

e.g. Insert a column between R and S and use formula =B2&C2 copied down.

Then alter the formula in what's now T2 to:

=MIN(IF($S$2:$S$603=S2,$A$2:$A$603))=$V$1

Where V1 now contains the input year.....

remember to confirm with CTRL+SHIFT+ENTER and copy down.

Hope that improves the speed a bit.....

Now, will that only work if I have the min year filtered beforehand?

No, but you will have to refilter after you change the year in the input field.

You can perhaps automate that with an event macro.