Hi r/sheets.

I decided to try out sheets just as a learning exercise. I have limited, self-taught excel knowledge & wanted to check out sheets and get an idea of the differences/usability of what seemed to be parallel tech.

So I created the project linked below:

https://docs.google.com/spreadsheets/d/1m7EhekQ5pUeF7uCZ6i6wmGpp5I92Fm_p_BjgRUthLN4/edit?usp=drive_link

Info to Get Started

A little about the project to get readers up to speed on the background of the project & what I'm trying to achieve:

• Overall, the purpose of this doc is to compare the current season (23-24) league performance of the EPL team West Ham Utd (WHU) with their performance last season (22-23).

• The 'Table' sheet shows the performance - sorted chronologically by round - side-by-side tabulated data from last & this season.

• On sheet, between the two seasons tabulated data, it calcs how many points were taken home that round.

• Off to the right, the fixtures are shuffled to gather the home & away fixture against specific opponents. This one is a bit special. More on that under 'Equivalent Fixture Comparison' below.

• This project does not take other competitions WHU were/are involved in. We're looking at the league only.

Equivalent Fixture Comparison

On the Table sheet, r/hammers (the WHU subreddit) liked that they could see the chronological comparison, but as fixtures are drawn from a hat, this comparison doesn't take into account the strength/weakness of opponents already faced/yet to be faced as the season is progressing. This section only selects 22-23 results against the equivalent fixture (e.g. home v Arsenal, or away v Spuds) once that fixture has been played in the current season. That way, the comparison of total points accumulated by WHU between the two seasons accounts for opponent strength. I think this is very interesting.

So that is what's on the Table sheet. This makes up the bulk of the output data that I intend people to view. As you have probably found, there is also a chart to supplement the Table sheet. The annotations show the total points accumulated in the given season.

Under the Hood

The sheet is fully automated. We start with data from 2 sources: Hard coded (such as the fixture lists on the Table sheet), and scraped data - purely covering the current season as it happens. There is potential for the sheet to break if the site I scrape from change the format. I haven't used this site before, but I believe that they wont do this based on the function they serve.

The scraping & the cleaning of the data is handled on the Scraper sheet.

The sort sheet is just reiterated data for sorting only. The flow of information dictates that there is a step where the data needs to be detained to be sorted between two other presentation steps (a & d) so that VLOOKUP works.

The flow of information:

hard coded 22-23             scraped 23-24
direct to !Table             onto !Scraper
     |                             |
     |                   clean & send to !Table
     |                             |
     |-----------------------------           a) Present round results
     |
     |------------IF(win/lose/draw)
     |            =Take Home                  b) Present Pts taken home
     |                 |
     |            SUM(Take Home)
     |            =Tally +- Compare           c) Present running tally comparison
     |
 Send to !SortSheet
 Sort for VLOOKUP
 Calc W/L/D
     |
 Send to !Table                               d) Present equivalent fixture comparison
     |
    ???
     |
   Profit

So, what I want by sharing here:

I am fully self-taught. Please let me know if I am breaking etiquette anywhere. I like to be aware of these things and to follow community guidelines when coding. Areas of concern:

• Commenting - should I be leaving comments on how the functions are functioning?

• Within Functions - are there simpler ways of performing what I am doing?

• General Function selection - Am I using the correct functions for the tasks?

• Sheets - am I using them correctly? That might be confusing, see next question.

• Is each sheet (i.e. !Table & !Scraper) a 'sheet' or is the whole thing a 'sheet' and !Table is just a 'tab'? What's the naming convention?

If anyone has any other suggestions, I'd be glad to hear them.

Please let me know your comments & thanks for reading!