I have a list as follows:
Column C:
1
2
3
7
8
9
12
13
14
15
30
31
How can I generate a list of all available numbers between 1 - 31 starting with the first available (4).
Thanks for your time.
I have a list as follows:
Column C:
1
2
3
7
8
9
12
13
14
15
30
31
How can I generate a list of all available numbers between 1 - 31 starting with the first available (4).
Thanks for your time.
Give this array-entered** formula a try...
=MIN(IF(C1:C11+1<>C2:C12,C1:C11+1))
**Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself.
Note: C11 is the next to last cell with data and, of course, C12 is the last cell with data.
EDIT NOTE: I just realized the above is not what you asked for.. it only finds the next available number, not all of the available numbers. I am not sure how find all of them with a formula... I can do it with VBA macro if that would be acceptable.
Last edited by Rick Rothstein; 08-19-2020 at 03:27 AM.
It establish a list of available number in C1:C100:
In E1:
Drag downPlease Login or Register to view this content.
Quang PT
Hello Aaron,
try this in D1, then copy down.
=AGGREGATE(15,6,ROW(C$1:C$31)/ISNA(MATCH(ROW(C$1:C$31),C:C,0)),ROWS(D$1:D1))
@bebo021999,
use of 1-COUNTIF will give incorrect if numbers repeating, you may need to change to (COUNTIF(...)=0)
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
For O365 users:
=FILTER(SEQUENCE(A12,,A1),ISNA(MATCH(SEQUENCE(A12,,A1),A1:A12,0)))
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks