Assitance please!! Find the missing sequence

[Pinonze]

Hello All,

Appreciate you helping - Please see attached excel sheet - I am looking to find the missing sequnce in column A, So in column B I need to know if the numbering skipped in column a and the cell in Column B to say missing or skipped or should some how indetify where it skipped.


Please Help

Missing Index Check1.xlsx

[Pinonze]

Missing Numbers.png

Posting an image as well just in case!

[Sam Capricci]

how would someone know what is missing? Is the sequence supposed to be 1.1.1.1 then 1.1.1.2 then 1.1.1.3 or 1.1.1.1.1 etc?

[Pinonze]

Thanks, Sambo - It varies starts from 1 then 1.1 then 1.1.1 then sometimes can skip to 1.2. I know this is a difficult one but any help would be appreciated.

Kind Regards

Andrew Nze

[Sam Capricci]

I looked at your sheet and I don't see any way to tell what is supposed to be there vs. what is missing because you have as many as 16 different periods (8.2.1.6.3.10.4.4.4.1.1.1.9.1.1.1.1)
and determining a sequence is beyond my ability. Maybe if you provide more detail about where or what generates the numbers.

[MrShorty]

I will agree with Sambo kid, there needs to be more careful description of what is expected. Perhaps as a start, here are a few missing sequences that I see:

1.1.1.2 to 1.1.1.9 are all missing
1.1.2.1 to 1.1.2.9 are all missing (same for 1.1.3 to 1.1.5)
1.1.6(.0) to 1.1.6.9 are missing (same for 1.1.7 to 1.1.9)
and so on.

Farther down the list, I see up to 17 number sequence, so is 1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1.1 also a missing sequence?

At this point, all I can see is a "generate all possible permutations of up to 17 numbers (from 1 to ??), and then identify which ones are not present. But that seems overboard, when the list is less than 20000 entries long -- there will be a lot of missing permutations. Though I would not be surprised if that is ultimately the algorithm that one would use here:

1) Generate/Identify all possible permutations (with very careful consideration of the rules for generating and identifying a possible permutation -- this might be the hardest part of this kind of algorithm).
2) Test if each possible permutation is present in the list.

[Glenn Kennedy]

Not a solution.... but maybe someone else can build on it.

I cut your data down to 100 rows, for ease. I then parsed the numbers by their "." separator. Mabe VBA or some formula can spot the non-continuous values. they do stand out better than in your original, but I wouldn't want to do 17,000 rows manually....

[Pinonze]

Thanks Sambo, Glen , Mr Shorty

Glenn your file is exactly what I am trying to achieve as the is no logic to the algorithms, Its just that where there is a sequnce of numbes - I just need to know if it skips a number. A loop might be needed here I think.

Thanks Guys for looking into this

[JohnTopley]

A VBA "solution": make no claim on its robustness but works for sample provided,

See sheet2 of attached

Please Login or Register  to view this content.