To all the Excel Gurus out there, and i Know there are a few of you. I have a spreadsheet containing about 25000 rows of data across about 10 Columns, I would like a quick and easy way to separate those rows into smaller sheets based on the info in one of the columns. There are about 60 different id's in that particular column, so like all the rows for each of those 60 id's separated into 60 different sheets. It is Horse Racing info and I want to separate it into different sheets based on individual Tracks.
Excel/Google Sheets Discussion
- Thread starter doc ron
- Start date
@Beijing_Sting @Ironhawk @warewolves @Beg2Differ @Goodie's Guns
We should (probably) rename this thread: I've got this data and I want to use it to do this.
Thought now days, how can I import this data and then do the above.
Happy to hear from any webscrapers who are prepared to share. Which we will do in the background.
We should (probably) rename this thread: I've got this data and I want to use it to do this.
Thought now days, how can I import this data and then do the above.
Happy to hear from any webscrapers who are prepared to share. Which we will do in the background.
I can get by in Excel, and can usually work out ways to get it to do whatever I need, but I know there are some gurus here who are far more proficient at it than I ever will be.
I mainly use some cheap Excel knock off on my own computer for my spreadsheets, it is basically a copy of a version of Excel from about 20 years ago. It does the job, but it doesn't have any of the newer functions, so it is a bit limited. I do have access to 365 on my work laptop if needed. Not meant to use it for personal stuff, but I do occasionally.
I mainly use some cheap Excel knock off on my own computer for my spreadsheets, it is basically a copy of a version of Excel from about 20 years ago. It does the job, but it doesn't have any of the newer functions, so it is a bit limited. I do have access to 365 on my work laptop if needed. Not meant to use it for personal stuff, but I do occasionally.
- Joined
- 22 Oct 2014
- Messages
- 7,882
- Likes
- 41,731
- AFL Club
- North Melb.
That illustrates it nicely however the drawback in using that approach is that you have to list every option individually and it would miss ones where the DPPs designations are in the reverse order. The following example is little more complex however it would pick up anything with "DEF" in the position.
=FILTER($G$3:$J$47,ISNUMBER(SEARCH("DEF",$J$3:$J$47)))
Note these are called spill functions which means they spill into as many cells down the page as required. If there is something else in the cells that this function wants to spill into then you will get a SPILL error.
A bonus tip which I have only recently stumbled across is if you enclose that function in the TEXTJOIN function and provide a delimiter then you get a list of all the items in a single cell.
=TEXTJOIN(", ", TRUE, FILTER(...))
=FILTER($G$3:$J$47,ISNUMBER(SEARCH("DEF",$J$3:$J$47)))
Note these are called spill functions which means they spill into as many cells down the page as required. If there is something else in the cells that this function wants to spill into then you will get a SPILL error.
A bonus tip which I have only recently stumbled across is if you enclose that function in the TEXTJOIN function and provide a delimiter then you get a list of all the items in a single cell.
=TEXTJOIN(", ", TRUE, FILTER(...))
I was using the following formula to get lists of players for each position from a master list.
=SORT(FILTER(FILTER(Season_Start_list,role_filter=A2,""),{1,1,1,1,0,1,1,1,1,0}),3,-1)
Season_Start_list is my named master list. This had 2 entries for each DPP player.
A2 had what position I was filtering on ie DEF
The 2nd FILTER is to only select a sub-set of the columns in the master list. ( 1= include, 0= exclude)
=SORT(FILTER(FILTER(Season_Start_list,role_filter=A2,""),{1,1,1,1,0,1,1,1,1,0}),3,-1)
Season_Start_list is my named master list. This had 2 entries for each DPP player.
A2 had what position I was filtering on ie DEF
The 2nd FILTER is to only select a sub-set of the columns in the master list. ( 1= include, 0= exclude)
