Excel - COLUMN Function Problem - Expert Solution

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
User 20/07/2018 - 02:45
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc