formula guru needed

[rbekka33]

Hi

i have a series of six character codes (3 letters, 3 numbers) and
need to do a consistency check to ensure that there are none missing i
the series. Does anyone know a formula which can test this and extrac
numbers which are not in sequence. Example below:

AAA001
AAA002
AAA003
AAB001
AAB003
AAB004
AAC011
AAC014

In this case I would need to know that AAB002 is missing and tha
AAC012 and AA013 are also missing.

I have a code to compare and extract unique codes but not to extrac
the missing number in a sequence. OR is there another way of doin
this. BTW - there are over 7000 rows in this column to check.

thank

 

[Rog]

Hi

This isn't possible withour writing code.

Is your email address the same without the "-nospam" part?
I'll send a workbook which will do this

Rgds

Rog

 

[sulprobil]

Hmm, I thought this is possible to solve with worksheet
functions. You will have to know the value range of each
letter and each digit here.

If letters can vary from A-Z and digits can vary from 0-9
then you would have to report 26^3*10^3 - 7000 =
17,569,000 missing values. Then a spreadsheet with 65,536
lines and 256 columns is not large enough, of course

But if we can narrow the number of combinations then I
think you could generate all combinations in a column and
look for them in next column with the match function, for
example.

Kind regards,
sulprobil

 

[JulieD]

Hi

you could check out Chip Pearson's page on duplicates to see if he has any
ideas you could implement
http://www.cpearson.com/excel/duplicat.htm

Cheers
JulieD

 

[sulprobil]

Let's give it a try;

Define the name MyRange for your 7,000 cells which contain
values AAA001, ...

Create a new sheet. Write in cells:

B1:
=MIN(CODE(MID(MyRange,COLUMN()-1,1))) and enter with
CTRL+SHIFT+ENTER

B2:
=MAX(CODE(MID(MyRange,COLUMN()-1,1))) and enter with
CTRL+SHIFT+ENTER

B3:
=B2-B1+1

Copy cells B1:B3 to columns C till D. Write in cell:

I1:
=IF(ROW()<=$B$3*$C$3*$D$3*$E$3*$F$3*$G$3,CHAR($B$1+MOD(INT
((ROW()-1)/($C$3*$D$3*$E$3*$F$3*$G$3)),$B$3))&CHAR($C$1+MOD
(INT((ROW()-1)/($D$3*$E$3*$F$3*$G$3)),$C$3))&CHAR($D$1+MOD
(INT((ROW()-1)/($E$3*$F$3*$G$3)),$D$3))&CHAR($E$1+MOD(INT
((ROW()-1)/($F$3*$G$3)),$E$3))&CHAR($F$1+MOD(INT((ROW()-1)/
$G$3),$F$3))&CHAR($G$1+MOD(ROW()-1,$G$3)),"")

J1:
=IF(ISERROR(MATCH(I1,Sheet1!
$A$1:$A$5,FALSE)),"Missing!","")

Copy I1:J1 down to row B3*C3*D3*E3*F3*G3 if you have this
number of rows

HTH,
sulprobil

 

[JWolf]

Try the following in B2 and copy down:

=IF(AND(LEFT(A2,3)=LEFT(A1,3),RIGHT(A2,3)-RIGHT(A1,3)>=2),IF(RIGHT(A2,3)-RIGHT(A1,3)=2,LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000"),"("&RIGHT(A2,3)-RIGHT(A1,3)-1&")"&LEFT(A1,3)&TEXT(RIGHT(A1,3)+1,"000")),"")

Results will be as such:

AAA001
AAA002
AAA003
AAB001
AAB003 AAB002
AAB004
AAC011
AAC014 (2)AAC012