Excel/Google Sheets Discussion

[jel]

@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.

Also interested to hear how people get their data. My method of a few years isn't working anymore.

 

[KLo30]

Also interested to hear how people get their data. My method of a few years isn't working anymore.

I've not seen any of the normal sources with a team picker or excel sheet this year. I've got a feeling that I'll need to manually get all the player prices.

 

[Beg2Differ]

I've not seen any of the normal sources with a team picker or excel sheet this year. I've got a feeling that I'll need to manually get all the player prices.

Thanks for the prod.

Attached are the players, positions and prices for the AFL season 2023 from the team picker. Note it is a tab delimited file.

 

[Ironhawk]

Thanks for the prod.

Attached are the players, positions and prices for the AFL season 2023 from the team picker.

Thank you.

I think @Beijing_Sting was after this data as well for our various comps.

 

[KLo30]

Thanks for the prod.

Attached are the players, positions and prices for the AFL season 2023 from the team picker. Note it is a tab delimited file.

Superstar

 

[Manikato1]

Thanks for the prod.

Attached are the players, positions and prices for the AFL season 2023 from the team picker. Note it is a tab delimited file.

Love your work. This time you are the cat and definitely not the kitty litter.

 

[Beijing_Sting]

Awesome timing as I will be on a break from work in a week. Thank you and cheers

Thank you.

I think @Beijing_Sting was after this data as well for our various comps.

Thanks for the prod.

Attached are the players, positions and prices for the AFL season 2023 from the team picker. Note it is a tab delimited file.

Awesome!

 

[doc ron]

Hi guys, need some help. I have attached a sample spreadsheet. It contains some basic horse racing data. The area shaded in blue identifies ONE race. The numbers in column G are the focus. They go from 1 to Max in sequence. The highest number represents the number of starters in that particular race. The first is highlighted in Orange. I need this particular number to be transfrred to all the cells in column H for that race, as shown in the example. So in essence, I need a formula I can put in column H to perform this task for the whole sheet, that is to say for every race. If you need clearer info then please ask me. Hopefully I've conveyed what I need.

Cheers Doc

Ok, the sheet won't attach so I will post a screenshot.

 

[froffershanners]

Would a =COUNTIF(A:A,A2) in H2 do the trick?

Then just drag that formula down?

 

[doc ron]

Would a =COUNTIF(A:A,A2) in H2 do the trick?

Then just drag that formula down?

Genius, thanks froffershanners. I knew there would be someone on here who could help.

 

[doc ron]

I was trying to work something based on column G without seeing the easier method based on A. I'm always looking at the complicated rather than the simple. Doh

 

[Spots]

Fairly sure when this site was created, this topic of conversation would not have been predicted!

 

[doc ron]

Hi guys, need some more help. I have two sheets that are almost identical but with some differences. Basically i want to merge the two sheets so that the data on each row in the second sheet transfers to where the data ends in the corresponding row in the first sheet. The data in columns D and E has to be the same in both sheets. This is what identifies which info should be on the same row, if you get my gist.

 

[KLo30]

Hi guys, need some more help. I have two sheets that are almost identical but with some differences. Basically i want to merge the two sheets so that the data on each row in the second sheet transfers to where the data ends in the corresponding row in the first sheet. The data in columns D and E has to be the same in both sheets. This is what identifies which info should be on the same row, if you get my gist.

Let's say the two tabs are called

with FootyWire feeding into AutoScores.

Then D would be =FootyWire!D1 and E would be =FootyWire!E1

 

[doc ron]

Sheet 1 is Win market info Sheet 2 is place market info. For example Sheet 1 Row 265, the data in Event info column and Selection ID column also appears in sheet 2 but not neccesarily in row 265. I need a formula in order to find the matching info from those two columns in sheet 2 and transfer that info into the matching row in sheet 1 at the end of that rows data. Sheet 1 has columns up to Q

 

[doc ron]

I could just copy and paste, but we are talking tens of thousands of rows of data.

 

[KLo30]

Sheet 1 is Win market info Sheet 2 is place market info. For example Sheet 1 Row 265, the data in Event info column and Selection ID column also appears in sheet 2 but not neccesarily in row 265. I need a formula in order to find the matching info from those two columns in sheet 2 and transfer that info into the matching row in sheet 1 at the end of that rows data. Sheet 1 has columns up to Q

I could just copy and paste, but we are talking tens of thousands of rows of data.

You ain't copying and pasting.

If I'm understanding correctly, I think INDEX and MATCH will grab the info you need. Similar to Vlookup but can grab info from columns other than the first.

 

[Beg2Differ]

This should work.

1. In the first empty column of the PLACE sheet create a reference column that is a concatenation of the identifying columns separated by another character (I generally use underscore). From above it seems that EVENT_ID and SELECTION_ID are the 2 relevant columns and appear to be in columns A and E. If row 2 is the first column with relevant data then that formula would be =A2&"_"&E2.
   
   
2. In the first empty column of WIN sheet use the XLOOKUP formula to get the relevant data from each column that you want as below.
   
   =XLOOKUP($A2&"_"&$E2,WIN!$I:$I,PLACE!A:A)
   
   
3. If you use the absolute references ($) as above and then copy it across you will pick up every column from the PLACE sheet. If there are duplicate colums you can just delete the columns you don't want.
   
   
4. If there is a possibility that the data is in the WIN sheet and not in the PLACE sheet you will get an error on those rows (#N/A). If you don't want that then you can add another argument to return something of your choosing. For example
   
   =XLOOKUP($A2&"_"&$E2,WIN!$I:$I,PLACE!A:A,"NO MATCH")
   
   
5. You will have to modify the sheet references from above to match what you are using but hopefully you can work that out from above.

 

[doc ron]

This should work.

1. In the first empty column of the PLACE sheet create a reference column that is a concatenation of the identifying columns separated by another character (I generally use underscore). From above it seems that EVENT_ID and SELECTION_ID are the 2 relevant columns and appear to be in columns A and E. If row 2 is the first column with relevant data then that formula would be =A2&"_"&E2.
   
   
2. In the first empty column of WIN sheet use the XLOOKUP formula to get the relevant data from each column that you want as below.
   
   =XLOOKUP($A2&"_"&$E2,WIN!$I:$I,PLACE!A:A)
   
   
3. If you use the absolute references ($) as above and then copy it across you will pick up every column from the PLACE sheet. If there are duplicate colums you can just delete the columns you don't want.
   
   
4. If there is a possibility that the data is in the WIN sheet and not in the PLACE sheet you will get an error on those rows (#N/A). If you don't want that then you can add another argument to return something of your choosing. For example
   
   =XLOOKUP($A2&"_"&$E2,WIN!$I:$I,PLACE!A:A,"NO MATCH")
   
   
5. You will have to modify the sheet references from above to match what you are using but hopefully you can work that out from above.

Brilliant, thanks buddy. A few modifiations, but ended up doing what I wanted it to do.

 

[doc ron]

Ok last question really.

I have a formula in column Q that totals the total of column R to a certain row based on data in another column. This total shows in column Q. In column P I want to get the percentage of each number in Column R compared to the total. If i put in something like =r2/q14 then it only works for the first event. I need a formula to change automatically when data appears in column Q.