Find missing items in a list of element

[darioit]

Hello,
here I'm again to ask for your help about a problem that I wouldn't like to solve with many excel steps, but i'm too jackass to do by myself a correct script, I'm sure you be in my help

The problem in very easy, find missing elements in a list of number, for example in this list below number 000002, 000004 and 000005 are missing, but sometimes this list are not sorted and also often I dont know which is first and last list number, for example

Input file:
field1;field2;aaaammggXX19000003;text;data
field1;field2;aaaammggXX19000006;text;data
field1;field2;aaaammggXX19000001;text;data

Desired Result:
XX19000002
XX19000004
XX19000005

Position of numbers are always in same column 26,6

Thank you as always in advance
Dario

[aGerman]

Position of numbers are always in same column 26,6

Sort the content beginning at the 27th character in each row. Extract the desired 6 digits and prepend a 1 to make it a decimal number. Always save the last row you computed to run a FOR /L loop for the missing numbers.

Code: Select all

@echo off &setlocal DisableDelayedExpansion
set "src=input.csv"
set "dst=missing.txt"

set "last="
>"%dst%" (
  for /f "delims=" %%i in ('sort /+27 "%src%"') do (
    if not defined last (
      set "last=%%i"
    ) else (
      set "row=%%i"
      setlocal EnableDelayedExpansion
      set /a "nxt=1!last:~26,6!+1,bfr=1!row:~26,6!-1"
      for /l %%j in (!nxt! 1 !bfr!) do (
        set "missing=%%j"
        echo XX19!missing:~1!
      )
      endlocal
      set "last=%%i"
    )
  )
)

Steffen

[darioit]

many thanks! Works great as usual !

[Aacini]

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Load numbers in a list and get the max
set "list= "
set "max=0"
for /F %%a in (input.txt) do (
   set "line=%%a"
   set /A "num=1!line:~26,6!-1000000"
   set "list=!list!!num! "
   if !num! gtr !max! set "max=!num!"
)

rem Show missing numbers from 1..max
for /L %%i in (1,1,%max%) do (
   if "!list: %%i =!" equ "%list%" (
      set /A "num=1000000+%%i"
      echo XX19!num:~1!
   )
)

Antonio