# Calc: compare 2 sorted columns, delta/changes in rank

Hello,

How can I set this up? Thanks!

Given two lists:

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

Result:

• 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 close merge delete

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.

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

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

Sort by » oldest newest most voted

Hello,

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)

Hope that helps.

more

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

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

( 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)

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

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

more