Finding missing values in a list made with a word with a number added...

[thumper300zx]

Is there a way to list missing values in the following list? The list is a word followed with a number, but some are deleted. The missing values are Example4 and Example7. I need to do this with a long list of tens of thousands usernames and find the missing values. Another more realistic example further below.

Example
Example1
Example2
Example3
Example5
Example6
Example8
Example9

BSmith
BSmith1
BSmith3
BSmith6
BSmith7
BSmith9

(Missing are BSmith2, 4, 5, 8)

The formula or code will need to be able to go through a whole column, display a list of missing name w/numbers otherwise looking through manually would be just as fast. It would also need to know to stop at the largest number in each unique name. (eg. stop with Example9 and BSmith9).

Thanks for any tips/answers/advice.

 

[Georgiboy]

What is the highest number that can be next to a name?

What vesion of Excel are you using this on?

 

[nemmi69]

This will find the numbers

Then in C

Could change "Missing" to B10+1

 

[nemmi69]

If your using the latest Excel

=LET(Vl,RIGHT(A11,LEN(A11)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A11&"0123456789"))+1),IF(OR(B10="",B11=""),"",IF(B11-B10=1,"",B10+1)))

 

[nemmi69]

=LET(Vl,RIGHT(A11,LEN(A11)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A11&"0123456789"))+1),IF(OR(B10="",B11=""),"",IF(B11-B10=1,"",B10+1)))

Never mind, that was rubbish

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Georgiboy]

I was trying to build a 365 option regardless but this is another solution where i am struggling a bit trying to convert from a VBA man to a Formula man. I wanted to do away with the seperate formulae and just have the 'Code' and 'Missing Numbers' columns, and then combine those two into one formula.

Below is where i got to with it, someone may be able to build on it to help the TS & me:

Book1
	A	B	C	D	E
1	Example		Code	Existing Numbers	Missing Numbers
2	Example1		Example	0, 1, 2, 3, 5, 6, 8, 9	4, 7
3	Example2		BSmith	0, 1, 3, 6, 7, 9, 10, 11, 12, 13, 15, 17, 18, 19	2, 4, 5, 8, 14, 16
4	Example3
5	Example5
6	Example6
7	Example8
8	Example9
9	BSmith
10	BSmith1
11	BSmith3
12	BSmith6
13	BSmith7
14	BSmith9
15	BSmith10
16	BSmith11
17	BSmith12
18	BSmith13
19	BSmith15
20	BSmith17
21	BSmith18
22	BSmith19
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=LET( nums,IFERROR(--BYROW(A1:A22,LAMBDA(x,RIGHT(x,LEN(x)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},x&"0123456789"))+1))),0), words,SUBSTITUTE(A1:A22,nums,""), UNIQUE(words))
D2:D3	D2	=LET( nums,IFERROR(--BYROW(A1:A22,LAMBDA(x,RIGHT(x,LEN(x)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},x&"0123456789"))+1))),0), words,SUBSTITUTE(A1:A22,nums,""), u,C2#, result,BYROW(u,LAMBDA(br,TEXTJOIN(", ",,FILTER(nums,words=br)))), result)
E2:E3	E2	=TEXTJOIN(", ",TRUE,LET(ts,--TEXTSPLIT(D2,", "),sq,SEQUENCE(,MAX(ts)),FILTER(sq,NOT(ISNUMBER(MATCH(sq,ts,0))))))
Dynamic array formulas.

 

[thumper300zx]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks -- I haven't been here much. I will get that done now.

 

[thumper300zx]

What is the highest number that can be next to a name?

What vesion of Excel are you using this on?

The currently largest number is 36. Hypothetically it could be endless.
O365.

 

[nemmi69]

This will find the numbers
View attachment 91020

Then in C
View attachment 91021

Could change "Missing" to B10+1

Sounds like this is your best approach. Change "Missing" to B10+1 to show the value missing.