find number that doesnt exist among others.

[TheBean]

Hi!
I have some sorting problems. I got a list with approximately 100-200 numbers...or more sometimes.

I have to find out how many times, for an exampel, number 2 exists.
And to take it a little bit further, i need to know wich numerbers that doesnt occur.

To find the smallest number, lets say 2, is easy,and the largest...hmm. ok, 399.
Then i need to list all the numbers inbetween, how many times they occur, and wich numbers that doesnt excist. Can thise be done?

Kind regards!

[Bryan Hessey]

Hi!
I have some sorting problems. I got a list with approximately 100-200 numbers...or more sometimes.

I have to find out how many times, for an exampel, number 2 exists.
And to take it a little bit further, i need to know wich numerbers that doesnt occur.

To find the smallest number, lets say 2, is easy,and the largest...hmm. ok, 399.
Then i need to list all the numbers inbetween, how many times they occur, and wich numbers that doesnt excist. Can thise be done?

Kind regards!

Hi.

with numbers in A1:A10, the formula
in B1

=LARGE(A$1:A$10,ROW())

in C1

=IF(COUNTIF(B$1:B$10,B1)=1,"",COUNTIF(B$1:B$10,B1))

in D1

=IF(B1=B2,"",IF(B1=B2+1,"",B2+1&" - "&B1-1))

should answer, as per the attached.

Adjust the range for the rows you have and formula fill to suit your data.

hth
---

[TheBean]

Thanks a lot!

[Bryan Hessey]

Thanks a lot!

Hi,

np, and thanks for the response.
---