Ask Your Question
0

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

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

rjr gravatar image

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 flag offensive close merge delete

Comments

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
1

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

Opaque gravatar image

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

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)

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

Hope that helps.

edit flag offensive delete link more

Comments

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
0

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

Comments

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 27 times

Last updated: Jun 27