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

thumper300zx

New Member
Joined
Jan 10, 2011
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What is the highest number that can be next to a name?

What vesion of Excel are you using this on?
 
Upvote 0
This will find the numbers
1683270538026.png


Then in C
1683270885258.png


Could change "Missing" to B10+1
 
Upvote 0
If your using the latest Excel

VBA Code:
=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)))
 
Upvote 0
VBA Code:
=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 :rolleyes:
 
Upvote 0
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’)
 
Upvote 0
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
ABCDE
1ExampleCodeExisting NumbersMissing Numbers
2Example1Example0, 1, 2, 3, 5, 6, 8, 94, 7
3Example2BSmith0, 1, 3, 6, 7, 9, 10, 11, 12, 13, 15, 17, 18, 192, 4, 5, 8, 14, 16
4Example3
5Example5
6Example6
7Example8
8Example9
9BSmith
10BSmith1
11BSmith3
12BSmith6
13BSmith7
14BSmith9
15BSmith10
16BSmith11
17BSmith12
18BSmith13
19BSmith15
20BSmith17
21BSmith18
22BSmith19
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=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:D3D2=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:E3E2=TEXTJOIN(", ",TRUE,LET(ts,--TEXTSPLIT(D2,", "),sq,SEQUENCE(,MAX(ts)),FILTER(sq,NOT(ISNUMBER(MATCH(sq,ts,0))))))
Dynamic array formulas.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top