Custom viewing/sorting/filtering

**b0x** · 03-10-2008, 11:57 AM

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** · 03-10-2008, 12:02 PM

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** · 03-10-2008, 01:07 PM

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** · 03-10-2008, 02:16 PM

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:

Please Login or Register  to view this content.

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

**b0x** · 03-10-2008, 02:37 PM

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** · 03-10-2008, 02:46 PM

Originally Posted by b0x

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.

**b0x** · 03-10-2008, 02:53 PM

I have mainly used small set of data, and have always just used the drag fill to do formulas and stuff... I am sure there is an easier way to get the same results on a large set of data.

Right now, after I copied it over to the main large file, I get false for everything... let me look it over again.

**NBVC** · 03-10-2008, 03:03 PM

Originally Posted by b0x

I have mainly used small set of data, and have always just used the drag fill to do formulas and stuff... I am sure there is an easier way to get the same results on a large set of data.

Right now, after I copied it over to the main large file, I get false for everything... let me look it over again.

You have to make sure to adjust the ranges to include all rows in the database and you must confirm the formula with CTRL+SHIFT+ENTER keys. You will see { } brackets appear around the formula....then you can copy it down.

**b0x** · 03-10-2008, 03:19 PM

So, if there are 43431 rows in the db, I need to use

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

assuming I added the name column in column S.

That would be for the entire db. Then, if I want to do 1930, I filter for 1930 and greater, right? And it should work?

Also, is there a way I can just tell it to copy down the whole column, and not drag it through 43k records?

**NBVC** · 03-10-2008, 03:22 PM

First make sure you have all records showing (i.e no filters applied)....then put that formula in T2.

Then without exiting that cell, hold the CTRL and SHIFT keys down and hit ENTER... you should see some { } curly brackets appear at the ends of the formula..... If so, then double-click on the little black square at the bottom right corner of the cell with the formula. It should copy it down to the bottom.

When you filter, you will be filtering for TRUE... this will be based on the year you enter in V1.

**b0x** · 03-10-2008, 03:48 PM

No dice. I did exactly what you said. I get the curly braces on the end. I did it unfiltered. I still get false on everything. Can I send you the full file? Its about 11 megs, which is way too big to post here... let me upload it to some webspace.

EDIT: HERE It is a totally unmolested copy.

Unless you have some space as well, you wont be able to send it back... but hopefully you can walk me through whatever you did, since I cant get it to work.

**NBVC** · 03-10-2008, 04:04 PM

ok... post the link here.

**b0x** · 03-10-2008, 04:12 PM

I just edited my above post, in case you didnt see it.

**NBVC** · 03-10-2008, 04:42 PM

You have to wait for the file to re-calculate... you will notice in the status bar that it is showing that it is calculating....

I'm not sure this will work for you though... you will be waiting everytime you change the input date....

Perhaps someone can offer a VBA solution that might be quicker?

**b0x** · 03-10-2008, 04:49 PM

I did wait... most of time time it just took forever, a couple times it totally locked up. And even after I waited, it still showed false for all of them.

**NBVC** · 03-10-2008, 04:56 PM

It works fine for me, using your sheet.

Try with 1885 as the input and make sure the re-calculations are complete... you should get a bunch of TRUEs in at least the first few hundred cells.

...but it is way too slow for my liking and it is the end of my day...so I can't think too straight... I can't see another solution other than perhaps VBA... I could be wrong... I could have another look tomorrow.

**b0x** · 03-10-2008, 04:59 PM

Ok, I will give it another shot... will you post the formula again, just so I can be sure. Thanks for the help.

**NBVC** · 03-10-2008, 05:01 PM

Please Login or Register  to view this content.

**b0x** · 03-10-2008, 05:22 PM

By the way, I think I got it working, but when I try to have it calculate the whole sheet at once, it totally kills my computer.... its been sitting for about 20 minutes now, still running. I can't imagine it took that long for you...well how about that... it just finished, after 20 minutes... like you said, not really feasible.

**NBVC** · 03-10-2008, 06:18 PM

Okay, I thought of another way on my drive home, that is much faster....

First, create the concatenated column (i.e. B2&C2, copied down the column). You can add a column header if you want.

Then, Sort the data by the concatenated column and then by the Season column. Sort both in Ascending order. This will take about a minute to sort.

Then put this formula in T2: =IF(S2=S1,T1,A2) and copy it down.

Now you can filter by column T. Choose the Start Date from the filter list. This should give you the records for the players that started the year you chose.

Hope this fixes the issue.

**b0x** · 03-11-2008, 10:28 AM

That works very well, but doesn't do exactly what I wanted. That basically pulls every player who was a rookie in 1930, i.e. the first season that shows up in the db for a given player is 1930. I wanted to look at every player who played in 1930, and look at their careers from that year forward, regardless of when they first showed up in the db. I think the other version accomplishes that, but it is painfully slow.

**NBVC** · 03-11-2008, 10:59 AM

Ok, how about this.

Setup your concatenated column in Column S and sort that column and the season column in ascending order as before...

Now enter the year of interest in U1.

And enter this formula in T2, copied down:

=IF(S2=S1,IF(A2=$U$1,A2,T1),A2)

Now filter this column for the year of interest.

Does this do it?

**b0x** · 03-11-2008, 11:10 AM

Hmmm.... that gave me ONLY the guys that have a last name starting with A. But it was the guys I wanted... just not all of them. Actually, there was zero overlap between the 2 most recent methods.

**NBVC** · 03-11-2008, 11:26 AM

Have a look at the attached...

I had to delete a bunch of lines to reduce the size... but you should get the idea.

Look at, for instance, Adams Sparky.... in column S, you will see that Row 31 to row 37 has a different year, but in Row 38 begins the input year of 1930.... so when you filter you will only see items in row 38 onwards for this player...

Is this not what you need?

**b0x** · 03-11-2008, 11:31 AM

That is exactly what I need, but not at all what I am getting... let me see if I can get it to work...

**NBVC** · 03-11-2008, 11:41 AM

Remember after you do the concatenation column, sort first by this column in ascending order, then by column A, in ascending order.

Then enter your input year in V1

and enter this formula in T2:

=IF(S2=S1,IF(A2=$V$1,A2,T1),A2) copy it down the column.

It should work.

**b0x** · 03-11-2008, 11:48 AM

Ok, it is working now. Thank you so much for your help, I really appreciate it. You saved me a BUNCH of time down the road.

This is for a sim-league baseball site. For this league format, we draft hitters from a given year, and then use their entire career. Like season 1, everyone uses the 1930 season, season 2 everyone uses the 1931 seasons, etc.

It is very fun, but there has never really been a good way to filter out the stuff you didnt want, which is what I was running into... you just had to manually go through and delete everything, which was a total pain, and had to be done for EVERY draft, since the start year is always different. This will make things much much easier.... now all I have to do is decide if I want to let other people know about this... or keep it all to my self

Thanks again.