excel - Find the missing ids that are in a specific pattern -

excel - Find the missing ids that are in a specific pattern -

i looking way find missing ids in range of cells. thing is, there pattern , not pure number - pw140000023. other thing might complicate search there duplicate ids, ok. found expression:

small(if(isna(match(row(a1:a30),a1:a30,0)),row(a1:a30)),row(a1))

though not yield results. how can modify needs?

example of missing ids:

pw140000023 pw140000023 pw140000025 'missing pw140000024 pw140000026

thanks

assuming info starts in a1, come in in b2 , re-create down:

=if(mid(a2,3,99)-mid(a1,3,99)>2,"missing pw"&mid(a1,3,99)+1&" - pw"&mid(a2,3,99)-1, if(mid(a2,3,99)-mid(a1,3,99)>1,"missing pw"&mid(a1,3,99)+1,"") )

output:

that bit of brute forcefulness solution.

here's shorter solution 32 characters, may bit confusing:

=trim(left( "missing pw"&mid(a1,3,99)+1&rept(" ",99)&"-pw"&mid(a2,3,99)-1, max(0,(mid(a2,3,99)-mid(a1,3,99)-1))*99) )

excel excel-vba excel-formula