Hello,
How can I set this up? Thanks!
Given two lists:
Result:
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.
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.
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.
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: Rank-UpDown.ods
Hope that helps.
This is elegant! I don’t yet have enough karma to upvote. Thank you!
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…
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.
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)
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. CompareAlphaValue.ods . Cheers, Al
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.
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.