Post by thebosma on Feb 16, 2020 12:46:11 GMT -6
Everyone, this has been so much fun. I've never taken responsibility for a project like this before but I had a 10/10 time tackling this. I also wanted to look at this as a trial run for the best of the decade list next month, especially because there are fewer films on here. I wanted to work out the kinks in the Excel program that I took a stab at putting together back in August. I made a bunch of edits along the way and made a few overhauls as we got everyone's actual results, but the most important thing is that it somehow fucking works and I couldn't be happier about it. Results will be out tomorrow in both a fun and interestingly styled way, but I wanted to make this thread and share the methodology that I used for this now, because I think some people may think it's cool/interesting. My dad did help me a bit, and I did tell him what it was for. He thinks you guys all seem cool, and he also said I was overcomplicating things for myself by using VLOOKUP, and then chuckled to himself.
As I dive into this I know people are going to say that it's a lot of work but the important thing is that it's versatile, can be used in the future, and I had fun putting it together.
To start out, I made three separate sheets within the excel document. The first was the raw data with everyone's lists, numbered 1-25. Each individual got their own column with their list ordered within it. This is the easy sheet to compile, I really just have to copy and paste everything, but I also made sure the formatting was the same for everyone. On some of it I got a little lazy. There were 5 different formats provided for John Wick 3 when you take into account spacing and punctuation. I standardized it as "John Wick 3" in this case. Likewise the new Star Wars became "Star Wars". What I didn't think about beforehand was how we would format Once Upon a Time... In Hollywood. That one was all over the place and rarely did two of you do it the same way. Hindsight is 20/20 but maybe I would have just picked one and mentioned it beforehand.
The fun part comes in with the second sheet which is titled "Scores". The scores page has the films listed on the y-axis, names listed on the x-axis, this time with the point values listed in the column rather than the film title. Obviously not everyone has the same films, so I wrapped the function that calculated the point value in an IFERROR function that would convert any error values to 0, so that the point values would add up without throwing an error (=IFERROR(26-MATCH($A4,Rankings!B$4:B$28,0),0)) was what I used but I'm almost certain there's a less clunky way to do this.
The third sheet is a second location I copied everyone's lists and then removed duplicates. This is where I was able to compile the y-axis list of the individual films. This was copy/pasted into the second sheet. It's worth noting that there were a few films that no matter how I formatted them, they wouldn't remove the duplicates. I was worried this would skew data but when they were copied into the second sheet they would show the exact same results for the exact same people, so I'm not entirely sure why that happened. It could be an extra space added after a title but I decided not to worry about it and ditched those columns for the actual data.
Everything can then be formatted as a table and sorted from highest number to lowest number, which will automatically give us the top 25.
For more fun with data, check back here tomorrow!!
As I dive into this I know people are going to say that it's a lot of work but the important thing is that it's versatile, can be used in the future, and I had fun putting it together.
To start out, I made three separate sheets within the excel document. The first was the raw data with everyone's lists, numbered 1-25. Each individual got their own column with their list ordered within it. This is the easy sheet to compile, I really just have to copy and paste everything, but I also made sure the formatting was the same for everyone. On some of it I got a little lazy. There were 5 different formats provided for John Wick 3 when you take into account spacing and punctuation. I standardized it as "John Wick 3" in this case. Likewise the new Star Wars became "Star Wars". What I didn't think about beforehand was how we would format Once Upon a Time... In Hollywood. That one was all over the place and rarely did two of you do it the same way. Hindsight is 20/20 but maybe I would have just picked one and mentioned it beforehand.
The fun part comes in with the second sheet which is titled "Scores". The scores page has the films listed on the y-axis, names listed on the x-axis, this time with the point values listed in the column rather than the film title. Obviously not everyone has the same films, so I wrapped the function that calculated the point value in an IFERROR function that would convert any error values to 0, so that the point values would add up without throwing an error (=IFERROR(26-MATCH($A4,Rankings!B$4:B$28,0),0)) was what I used but I'm almost certain there's a less clunky way to do this.
The third sheet is a second location I copied everyone's lists and then removed duplicates. This is where I was able to compile the y-axis list of the individual films. This was copy/pasted into the second sheet. It's worth noting that there were a few films that no matter how I formatted them, they wouldn't remove the duplicates. I was worried this would skew data but when they were copied into the second sheet they would show the exact same results for the exact same people, so I'm not entirely sure why that happened. It could be an extra space added after a title but I decided not to worry about it and ditched those columns for the actual data.
Everything can then be formatted as a table and sorted from highest number to lowest number, which will automatically give us the top 25.
For more fun with data, check back here tomorrow!!
Parasite 432
Uncut Gems 315
Marriage Story 293
Midsommar 281
The Irishman 273
Little Women 219
Us 194
Knives Out 186
Avengers: Endgame 181
Booksmart 175
Ad Astra 133
John Wick 3 122
The Beach Bum 106
Pain and Glory 96
The Lighthouse 95
1917 80
Once Upon a Time… In Hollywood 76
The Farewell 72
Under the Silver Lake 70
Joker 68
Hustlers 59
Toy Story 4 59
High Life 56
Transit 53
Star Wars 49
Ready or Not 45
Spider-man: Far From Home 45
Captain Marvel 45
Detective Pikachu 45
Ash is Purest White 44
Dolemite is My Name 39
The Souvenir 37
Atlantics 34
Shadow 34
High Flying Bird 33
El Camino 32
Waves 31
The Last Black Man in San Francisco 30
One Cut of the Dead 29
Climax 26
A Hidden Life 25
Knock Down the House 23
Jojo Rabbit 21
Ford vs. Ferrari 21
American Factory 20
Monos 19
Martin Eden 18
Serenity 18
Knife + Heart 18
Wrestle 18
Uncut Gems 315
Marriage Story 293
Midsommar 281
The Irishman 273
Little Women 219
Us 194
Knives Out 186
Avengers: Endgame 181
Booksmart 175
Ad Astra 133
John Wick 3 122
The Beach Bum 106
Pain and Glory 96
The Lighthouse 95
1917 80
Once Upon a Time… In Hollywood 76
The Farewell 72
Under the Silver Lake 70
Joker 68
Hustlers 59
Toy Story 4 59
High Life 56
Transit 53
Star Wars 49
Ready or Not 45
Spider-man: Far From Home 45
Captain Marvel 45
Detective Pikachu 45
Ash is Purest White 44
Dolemite is My Name 39
The Souvenir 37
Atlantics 34
Shadow 34
High Flying Bird 33
El Camino 32
Waves 31
The Last Black Man in San Francisco 30
One Cut of the Dead 29
Climax 26
A Hidden Life 25
Knock Down the House 23
Jojo Rabbit 21
Ford vs. Ferrari 21
American Factory 20
Monos 19
Martin Eden 18
Serenity 18
Knife + Heart 18
Wrestle 18
