Ask Your Question

Calc: compare 2 sorted columns, delta/changes in rank [closed]

asked 2020-06-27 04:28:13 +0200

rjr gravatar image

updated 2021-04-22 00:56:21 +0200

Alex Kemp gravatar image


How can I set this up? Thanks!

Given two lists:

  • A A
  • B D
  • C B
  • D E


  • A A 0 (showing no change)
  • B D +2 (showing D moved up in rank two spots)
  • C B -1 (showing B moved down in rank by one spot)
  • D E !! (showing E is new to the list)

These are not numbers, but well-defined text entries that are already accurate and consistent.

I don't care that C dropped off the list.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-04-22 00:56:10.563155


Just a remark: The term "sorted columns" and the example for the first column may be misleading here. The way the elements are put into two different sequence seems to be arbitrary. Now it would be helpful to understand for what reason elements as well can vanish as they can appear like just born.
Basically this (death/birth) may spoil any ranking in a sense that ranks before death/birth cannot simply be compared by calculating the difference to ranks after.
I would be interested to get this explained.

Lupp gravatar imageLupp ( 2020-06-27 15:46:09 +0200 )edit

Right! Sorry. The columns are NOT sorted, and I do not want to sort them (they are actually sorted before this point, based on other criteria). As far as the birth/death, I only want to compare the two columns. I only care about births, not deaths.

rjr gravatar imagerjr ( 2020-06-27 19:39:53 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-06-27 13:16:44 +0200

Opaque gravatar image

updated 2020-06-27 13:26:47 +0200


you may use: =IF(ISNA(MATCH(B1;A:A;0)-MATCH(B1;B:B;0));"!!";MATCH(B1;A:A;0)-MATCH(B1;B:B;0)) (provided that values are unique in each columns)

See also: C:\fakepath\Rank-UpDown.ods

Hope that helps.

edit flag offensive delete link more


This is elegant! I don't yet have enough karma to upvote. Thank you!

rjr gravatar imagerjr ( 2020-06-27 22:47:25 +0200 )edit

What if the lists have intervening data between the elements in question? In other words, each "entry" has 5 cells, but we only care about the first one. In other words, going down the list, cells 1, 6, 11, 16...

rjr gravatar imagerjr ( 2020-06-27 22:50:45 +0200 )edit

I don't yet have enough karma to upvote. Thank you!

But you can click the check mark (✔) next to the answer, without any karma to indicate a correct solution.

What if the lists have intervening data between the elements in question?

Did not test but this shouldn't matter as long as the number of cells in between two interesting values is the same in all columns (in fact: in between cells are whole in between rows) and the number of in between rows is constant (e.g. 5 as per your example)

Opaque gravatar imageOpaque ( 2020-06-28 12:23:24 +0200 )edit

answered 2020-06-27 07:33:14 +0200

Earnest Al gravatar image

Hi. I always revert to what I know, I expect I have made this longer than it should be. I have a assigned a value to every letter up to Z in Sheet 2. The calculation looks at the letter in B1and compares it to column A:A to see if it exists, if it doesn't then it returns !!. If it does find it, then it subtracts the assigned value of A1 from B1. C:\fakepath\CompareAlphaValue.ods . Cheers, Al

edit flag offensive delete link more


This works! But the A, B, C, D vary in what the actual text is, so Sheet 2 makes it too hard to assign values to unknown incoming text.

rjr gravatar imagerjr ( 2020-06-27 22:53:15 +0200 )edit

Question Tools

1 follower


Asked: 2020-06-27 04:28:13 +0200

Seen: 95 times

Last updated: Jun 27 '20