Question description:
This user has given permission to use the problem statement for this
blog.
Is there a way to compare all rows to each other? I have a large 3000 row spreadsheet, and on one column, I would like to compare row 1 vs 2, 1 vs 3, 1 vs 4, 1 vs 5, 1 vs 6.....2999 vs 3000.
I know, that's 5 million combinations.
I will be dividing the smaller number into the larger number, to get a percentage/correlation.
Solved by O. Q. in 32 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
20/07/2018 - 02:45
Yes
User
20/07/2018 - 02:45
1-5
Excelchat Expert
20/07/2018 - 02:46
Can I ask your goal why you need to compare rows with each other, so I can think of a different way to assist you?
User
20/07/2018 - 02:48
Sure I have a list of player statistics, and Im comparing player A to player B, and so forth. I want to see which players are most similar to each other--I can show you in the spreadsheet
Excelchat Expert
20/07/2018 - 02:48
Ok, please.
User
20/07/2018 - 02:50
So, the bottom row is all my "percent similar", and when you average those out, these two players are 89% similar
User
20/07/2018 - 02:50
The math is easy enough...the problem is...I have 3000 players to compare
Excelchat Expert
20/07/2018 - 02:51
ok let me think real quick.
User
20/07/2018 - 02:52
That "89% final comparison" number is what I want to be able to pull--
User
20/07/2018 - 02:52
essentially--out of all these 3000 players, which players are most like Player 500. What players are most like player 2000, etc
Excelchat Expert
20/07/2018 - 02:52
Where can I see that?
Excelchat Expert
20/07/2018 - 02:53
Let me think of a solution
User
20/07/2018 - 02:53
Im scraping data off of xstats.org
Excelchat Expert
20/07/2018 - 02:54
You need to compare all their statistics? Not just their overall average?
User
20/07/2018 - 02:55
Just the overall number, the problem is the overall number isnt found until compared to another player
User
20/07/2018 - 02:57
eh there was supposed to be a chart on that, comparing the two players...
User
20/07/2018 - 02:57
it didnt paste properly
User
20/07/2018 - 02:57
that chart I want to be able to create for any two players
Excelchat Expert
20/07/2018 - 02:58
Yes, I can see it. You just want to have something that can easily compare the players, right?
User
20/07/2018 - 02:58
yes, and see which players are most/least similar
Excelchat Expert
20/07/2018 - 02:59
Ok, give me a moment. Feel free to extend our session.
Excelchat Expert
20/07/2018 - 03:02
Oh, I wanted to let you know that I'm working on my microsoft excel so you won't see any updates in the sheet.
User
20/07/2018 - 03:02
ok thank you
Excelchat Expert
20/07/2018 - 03:03
What's your formula for cell B5?
Excelchat Expert
20/07/2018 - 03:03
Just divide?
User
20/07/2018 - 03:03
=IF(S5<S6,S5/S6,S6/S5)
Excelchat Expert
20/07/2018 - 03:04
Ok, thanks.
User
20/07/2018 - 03:04
That way the number will always be <1
Excelchat Expert
20/07/2018 - 03:04
Understood,.
User
20/07/2018 - 03:05
Sorry, that fomula didnt translate in copy/paste, my cells are aligned differently, but yeah, you understood
Excelchat Expert
20/07/2018 - 03:05
Oh, no worries.
User
20/07/2018 - 03:05
Ultimately, my goal is to look like this:
User
20/07/2018 - 03:05
https://www.mockdraftable.com/player/marcus-davenport
Excelchat Expert
20/07/2018 - 03:06
Do you think it would work if I compare their AVGs first
User
20/07/2018 - 03:06
The chart I have exact, its the "here are players that are close" that Im efforting on
Excelchat Expert
20/07/2018 - 03:06
then OBP
Excelchat Expert
20/07/2018 - 03:06
then SLG?
Excelchat Expert
20/07/2018 - 03:06
and so forth?
User
20/07/2018 - 03:06
wouldnt that just be more processign power? I have them all being compared seperately, and then brought together to form the .89
Excelchat Expert
20/07/2018 - 03:07
Hmm, because I'm thinking I'll create a sheet per stat
Excelchat Expert
20/07/2018 - 03:07
and the the final sheet will collate all the numbers.
User
20/07/2018 - 03:08
Ahh thats clever. Multiple sheets. I hadnt thought of that
Excelchat Expert
20/07/2018 - 03:08
So you'll have something like this
Excelchat Expert
20/07/2018 - 03:09
Please look at Sheet2
User
20/07/2018 - 03:09
I am
Excelchat Expert
20/07/2018 - 03:09
something like that.
Excelchat Expert
20/07/2018 - 03:10
Then OBP
Excelchat Expert
20/07/2018 - 03:10
Since you have all the side by sides
Excelchat Expert
20/07/2018 - 03:10
the last sheet can compute their totals for all stat, what do you think?
User
20/07/2018 - 03:10
ahh ok! Then I just use index/match to locate each cell
Excelchat Expert
20/07/2018 - 03:11
Ohh, I see you're proficient in spreadsheets yourself.
Excelchat Expert
20/07/2018 - 03:11
That's correct. Or however you want it since you have all the comparisons.
User
20/07/2018 - 03:11
enough, I was just stumped over how to comb over 5 million combinations at once haha
User
20/07/2018 - 03:11
thats great!
Excelchat Expert
20/07/2018 - 03:12
Would that work for you?
User
20/07/2018 - 03:12
Currently, my data looks like Sheet 3
Excelchat Expert
20/07/2018 - 03:12
Also, instead of =IF(S5<S6,S5/S6,S6/S5)
User
20/07/2018 - 03:13
and im using this formula to pull data over
User
20/07/2018 - 03:13
=INDEX(Sheet1!X9:X3760,MATCH(1,IF($H$5=Sheet1!$A$9:$A$3760,IF($D$5=Sheet1!$D$9:$D$3760,1)),0))
User
20/07/2018 - 03:14
So multiple pages would actually help clear it up, make it look a lot smoother, and easier to navigate
User
20/07/2018 - 03:15
I appreciate the help! (What were you saying about instead of =if(25<...)
Excelchat Expert
20/07/2018 - 03:15
Ok, what help can I provide you now that we've established that?
Excelchat Expert
20/07/2018 - 03:15
Oh I was just about to suggest =MIN(S5:S6)/MAX(S5:S6) but it's just the same. Just realized you're good at spreadsheets.
User
20/07/2018 - 03:16
Ahh ok. Thank you for the help, Im off to get this mammoth project done
Excelchat Expert
20/07/2018 - 03:17
Ok ok, if that's all then I wish you have a very great day ahead of you.
Excelchat Expert
20/07/2018 - 03:17
Thank you for using Got it Pro!
User
20/07/2018 - 03:17
Have a good one!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
