Determine Missing #s in columns

I have a column F with 71 rows in it. The cell value can vary between 1-26. What I am trying to do is have formula that will tell me what is 'missing' from col f to make a 'complete' series

For ex:
1
2
4
5
7
8
9
etc...through 26
in this case / example I am missing 3 and 6. If series is complete - (I have all 26 numbers), to display - all good. This column is not sorted.

Any ideas?

Thanks Spitfireblue,

Was hoping to display results in another cell automatically as values are entered in the f col.

Flame,

I have included a sample of data, and what I am waiting the output to be. I have also included a sample formula that displays the array correctly but not outputting correctly.
Thanks again!
Jack

Yes yours worked perfectly. That was just something I was trying to test. It looks like I will have to a helper celsl(s) to do what I want. I am attempting to bring the results to a single cell For example:


cell j75 - 1,6,9,12,13,16,21,24,25,26 in a a single cell. Issue is that I dont know how many numbers will be displayed, it could be 1 number or 26 depending if there is any data included. I have uploaded another exaple sheet to show what I mean - Your soution / formula is on the far right

Flame,

I apologize for long delay. You solution worked perfectly with the VB code. I got all to work. However I do need to throw one more monkey into to the equation.......

I currently have a 'test' account that is also in the list (f1:f71)- it gets his number from same page that I added your code and drug down.

For example this week his # is 15. I need it to count this twice for it be counted once, my issue is that number may change week to week. I want 15 to display until a valid 15 is entered.

You will see what I am saying in example we have used - q1. Basically it needs to count this # twice before it can say it is missing from column.

Thanks again!

Flame,
Ok lets start over from the top. I currently have a col (f) on page one that has various #s I through 15. One row is a test acct that ALSO has in same col (F) The test acct # is done various formulas, so I never know what its going to be. I did as you instructed,
=IFERROR(SMALL(IF(ISNA(MATCH(ROW(sheet1!$1:$15),sheet1!$F$2:$F$71,0)),ROW(sheet1!$1:$15)),ROWS(sheet1!$1:1)),"")
and drug down on sheet 2 - this worked perfectly. I then mentioned that I wanted to display 'missing' numbers in a cell on sheet one - You provided me with the concatall code - this is what I have on page one:
=concatall(IF(COUNTIF($F$2:$F$71,ROW($F$1:$F$15))=0,ROW($F$1:$F$15),""),",")

AGAIN all works perfectly. However for proper stats I need to have test acct f col counted twice before sheet declares that it is missing. Currently It shows #15 as 'not missing' because of test acct - when actually it is. - does that make sense? I currently have test acct (cell f from page 1) info on sheet 2 in its own cell.

Flame,
Very observant! Sheet is actuallu 2 sheets, in one (6 sheets) One that I am speaking of does 1-15 and other does 1-26. I only assumed formula could be carried to other.

Pal...Why you got 'Retired' in your name..... Those brain cells are kicking more than ever, my friend!

Jack

Ok Flame,

Your solution DID work for the concatall part But believe I may of been over thinking and explaining the process. I have since created a new test sheet. PLEASE disregard all others. I believe you will get a better understanding of what I am attempting to do.

Flame,
We getting somewhere. I did tweak your line of code:
=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:15"))&"",IF(COUNTIF($F$2:$F$25,$N$1)<2,SUBSTITUTE($F$2:$F$25,$N$1,""),$F$2:$F$25&""),0)),ROW(INDIRECT("1:15"))),ROWS($2:2)),"")

for other sheet I changed the 15 to 26.... All works fine. Now thats out of the way.
I cant seem to get the concatall portion to work as it was before. To simply avoid confusion
How do I get YOUR results from col q to displayed in sgl cell? HORT......

Flame,

Nevermind...I got...Thank you so much!

Flame,

I promise, this will be last question. Now that you see what I was try to solve with the test acct(N1) - needing to be counted twice to be considered missing.How would I do same with another value - say in o1 You have to remember that these could possibly match. so would I do something maybe with an 'or' statement in code below

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:15"))&"",IF(COUNTIF($F$2:$F$25,$N$1)<2,SUBSTITUTE($F$2:$F$25,$N$1,""),$F$2:$F$25&""),0)),ROW(INDIRECT("1:15"))),ROWS($2:2)),"")

Flame,

Got a new sheet for ya. As I stated, It works perfectly for n1 - I need to do same for o1. While rare, n1 and o1 could be same since these are dynamically driven.

In the scene N1 shows as it should cause count is less than 2 However I also need to have o1 included in col q list (it currently at count 1). If by chance that n1 and o1 are same - then that means there would have to be actually 3 instances before coming off missing list

Thanks again!
Jack

Correct... THERE will NEVER be a higher 15 (sheet1) or 26 on sheet2. I changed them respectfully and all works fine... for N! Now I need to include o1 to the list in col q - only if count <2 However if n1 and o1 are same obvisously count would need to be <3 to show up on 'missing list'

those sparks are still flying! Appears to work so far.....THANK YOU!!! (yes I was yelling - long way to way IL!)

Jack

Flame,

I promised no test accts. All is good! But for some reason I have continued thinking about it all day. Now that you know the results I wanted. Would it have been easier to concatall the missing numbers - test accts would of not been there for 1st entry either by user user test. Then add those tests to the concatall cat list? With the variable that those numbers would have to be counted 2? Is my logic off? Does that make sense? Am not making any changes, just thinking outside the box and I may be entirely on logic as my original request did not provide enough info. You were great I really appreciate it, my friend!
Stay warm!
Jack

Flame,

All is good.........I was just thinking of simpler way of achieved answer I wanted - is correct with the substitutes. Y thinking was that the test accts were already 'missing' from concatall list. Would it not of been easier to 'add' those back to concatall list if not picked by regular users? Am I wrong with my logic? Trust me I am learning - and a lot from you.... Was just being curious. I dont plan to change a thing!!!!!

Flame,
Noticed a error with the formula. I have submitted a brand new example of what I am speaking of. I think this should give a overall better example.

I believe culprit to be in the rows that it starts the counts??? I hope you can take a look and tell me what the heck is going on?

Thanks,
Jack