Ask Your Question

Adding Blank Rows for Sequential Missing Numbers

asked 2018-10-02 04:37:42 +0200

chrchcol gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-02 10:42:13 +0200

updated 2018-10-04 20:31:43 +0200

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.

image description

edit flag offensive delete link more


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!

chrchcol gravatar imagechrchcol ( 2018-10-04 09:38:48 +0200 )edit

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.

SM_Riga gravatar imageSM_Riga ( 2018-10-04 20:32:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-02 04:37:42 +0200

Seen: 134 times

Last updated: Oct 04 '18