Adding Blank Rows for Sequential Missing Numbers

[chrchcol]

I have a large database import I am working with that is several tables of data that have to be combined into one master spreadsheet(csv) for import.

There is a primary key with both tables but both tables are missing numbers say the order is 11-83529 but neither table is completely sequential.

The only solution I have found the works on that column is to fill - series choosing linear and using the line numbers.

However I need to act that way on the whole , adding a row each time it finds a missing number and adding the number to the first column? Does anyone know how to do this?

[SM_Riga]

Hello. Please find sample file with instructions attached - Fill missing IDs The idea is to create range with all IDs needed, then run test to find missing IDs, add them to ID column and sort this column in ascending oder. Please be aware that working with the large data ranges is resource consumable, some calculations can take few minutes to complete - depends on your hardware.

Edit 04.10.2018:

Based on your comment, please see this screenshot.

[chrchcol]

Suppose using the worksheet you provided Z and not C was the field after all your columns that you put the correct sequential numbers and AA was the field you chose to place the formulas. Also suppose that I am only using 11-25 of my actual spreadsheet with the missing row numbers of 20 and 21 in the original sheet just to cut down the memory and run some tests.

How would I update the match formula?

Btw Thank you so much!

[SM_Riga]

Please see my answer’s edit - assuming column Z contains range with all the correct IDs (Z2:Z16), column Y is the range with missing IDs (Y2:Y14), the MATCH formula in AA2 cell shall be =MATCH(Z2;$Y$2:$Y$14;0) This formula searches for Z2 cell value in Y2:Y14 range. If it returns #N/A, this means value is not matched.