Total number of unique matches in two columns

Column A = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison
Column B = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison

Need: 1 cell that compares all of the A range to all of the B range and returns a a percentage of repeats. So, if there are 50 entries in A, and only 40 are unique, and B includes 10 repeats (unique, not multiple), then the returned value would be 25%.

Sample posted.

Originally Posted by StephenR

One of the few formulae I can remember:

=SUM(N(FREQUENCY(B2:B8,B2:B8)>0))/SUM(N(FREQUENCY(A2:A11,A2:A11)>0))

Duh...your formula works for what I posted...which was wrong. Look again. I need to figure out how many REPEATED unique numbers occur and percent that against the original unique numbers list.

If I understand you correctly, the number in B19 is arrived at by counting the number of times a number in B2:B8 occurs more than once and where the number occurs in A2:A11. Is this correct?

Originally Posted by Domenic

If I understand you correctly, the number in B19 is arrived at by counting the number of times a number in B2:B8 occurs more than once and where the number occurs in A2:A11. Is this correct?

The list in A only has 7 unique numbers in it. I don't need to know how many times a number occurs, just how many unique numbers are there. The real list could be longer or shorter each month.

Then, in B, I find only 5 unique entries. Again, the REAL list could be of any length.

Then I need to find how many of the unique entries in B also occur in A. I can express that as a percentage, I just need to know if that can be calculated in a single cell?