How to Find Missing Items in a Column with Consecutive Numbers in Excel Worksheet

When you are working on an Excel file, it is unavoidable to lose some data and information. In this article, we will show you 3 methods to find the missing items in a column with consecutive numbers.

Usually in your Excel worksheet, there will be a column with consecutive numbers. However, some reasons will cause some data to be lost, such as copying and pasting, or you forget to collect a sales representative’s information. Therefore, it is necessary for you to check the worksheet in your work.

In this worksheet, the first column contains the employee ID. They need to be consecutive logically. However, the last employee ID is “20150018”, while the corresponding row is 16. This means that there are 3 missing items in this worksheet.

If you check numbers manually and see the numbers one after another, you will spend a lot of time and energy, especially when there are many items in the column. In order to find those missing items quickly and easily, you can use the three methods in the following parts.

Method 1: IF Function

In this method, you will need to use the IF function for the worksheet.

1. Click a blank cell in the worksheet. In this example, we will click the cell E3 in the worksheet. You can also select a cell according to your need.
2. And then input this formula into the cell:

=IF(A3=A2+1,””,”lose data”)

This formula will judge the relationship between the adjacent two cells.

3. After that, press the button “Enter” on the keyboard. If there is a missing item or there are several missing items, you will see the result “lose data” in the cell. Else you will see nothing.
4. Now click cell E3 again.
5. And then double click the fill handle of this cell. Therefore, you will fill the whole column with the same formula. And you can see the result in the worksheet.

There are two cells with the result of “lose data”. Thus, you can check according to the result of this formula. Come back to the corresponding cells in column A, you will know that the missing items are “20150003”, “20150010” and “20150011”.

Method 2: Conditional Formatting

Except for the IF function, you can also use function with conditional formatting in the worksheet.

1. Select the target column in the worksheet.
2. And then click the button “Conditional Formatting” in the toolbar.
3. In the drop-down list, choose the option “New Rule”.
4. In the newly pop-up window, choose the last option about using a formula.
5. And then input this formula into the text box:

=NOT(A2+1=A3)

This formula will also judge whether the second number is the result of the first number plus 1.

6. After that, you need to set the format. Here click the button “Format” in this window.
7. And then you will see the “Format Cells” window pop up. In this window, you can set a special format for the target cell. You can set according to your preference. And here we will change the fill color for the target cell.
8. When you finish the setting in the “Format Cells” window, click the button “OK” in the window.
9. Next in the “New Formatting Rule” window, continue click “OK” to save the setting of this new rule.

Now you will come back to the worksheet. You will see that if there are missing items, the format of the target cell will change.

Here the format of the last cell A16 also changes. This is because there is no value in cell A17. Therefore, you can just ignore this cell.

Method 3: VBA Macros

Except for the above two methods, you can also use VBA macros to fulfill this task.

1. Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
2. And then insert a new module in the editor.
3. Now copy the following codes and paste into the module:

Sub FindMissingItems()
  Dim RowReference As Integer
    For RowReference = 2 To 16
      If Not Cells(RowReference, 1) + 1 = Cells(RowReference + 1, 1) Then
        Cells(RowReference, 1).Interior.Color = vbYellow
      End If
    Next RowReference
End Sub

In this macro, you need to change certain elements according to your actual worksheet. And we will format the cell with the yellow color.

4. In this step, you will need to run this macro. There are different methods to run macros in Excel. You can press the button “F5” on the keyboard to run macro. And the result will already appear in the worksheet.
5. Now come back to the worksheet and check the result.

The result is actually the same as the result of conditional formatting. And just by pressing the button on the keyboard, you can get the result immediately. On the other hand, you also need to pay attention to one point. If there is a rule of conditional formatting in this column, the result of conditional formatting will cover the result of VBA macros. Hence, you need to clear the conditional formatting rule in the same range.

A Comparison of the Three Methods

When you need to check a column in a worksheet, you may have trouble in choosing the methods. The table below lists all the advantages and disadvantages of the three methods. You can refer to it to have a better understanding of those methods.

Comparison	IF Function	Conditional Formatting	VBA Macros
Advantages	This method is very easy to understanding. Every user can use this method in their worksheets.	The result will be produced in the original worksheet, so that you can easily find the missing items.	By just one click, you can get the result in the original worksheet.
Disadvantages	By using this method, you need to produce result in other range. You may find it irritating to switch between two different ranges.	Every time you use this method, you have to perform all the steps all over again. And this is very time-consuming.	You will certainly need to modify the codes according to actual worksheet. If you are not familiar with VBA codes, you will meet with problems.

The next time if you need to find missing items or check in a worksheet, you will know which one is the best method for you. By using any of a method, you can finish your task easily and quickly without any errors.

The Importance of Backing up Your Excel Files

We all know that the data and information in Excel files are very essential. Therefore, you need to pay special attention on those important files. There are many methods that you can take to protect your files. And backing up files is also one method. When your Excel files are damaged due to some reason, you still have the backed up version. All the data and information is still safe. However, sometimes the backups will also be damaged. At this moment, you need to repair Excel files. You can use our powerful Excel recovery tool to repair Excel xlsx data corruption. With back up files and this recovery tool, your files will certainly be safe.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc data and outlook repair software products. For more information visit www.datanumen.com