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