Insert blank rows for missing values

Author: Oscar Cronquist Article last updated on January 27, 2019

I have 2 columns named customer (A1) and OR No. (B1).
Under customer are names enumerated below them. opposite the name of customers are OR No. issued to various customers.
OR No. is in broken sequence.

My question is, how will I insert the rows for the corresponding missing OR numbers?

Example:
(A1) (B1)
Customer OR No.
customer 1 1
customer 2 2
customer 3 5
customer 4 7
customer 5 8
customer 6 10
customer 7 11
customer 8 13
customer 9 14
customer 10 15

Answer:

The solution presented below does not insert blank rows for missing values. I am going to create a new list based on the old list, however, it will have blank rows for missing values.

We don't need to use a macro if we do it this way, a simple formula is enough.

Create new OR numbers

Type 1 in cell B2.
Select cell B2
Press and hold with right mouse button on black dot on cell B2.
Drag down to cell B16.
Press with left mouse button on "Fill series"

Match OR number and return customer name

Formula in cell A2:

=IFERROR(INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2, Sheet1!$B$2:$B$11, 0)), "")

Copy cell A2 and paste to cell range A3:A16.

This formula matches the OR number and returns the Customer. If a customer is not found, the cell becomes blank.

Explaining formula in cell A2

Step 1 - Find position of given value in column

The MATCH function returns the relative position of a specific value in a list. It returns the first position of the first instance found, if duplicates in the list.

Note, it is important to use a 0 (zero) in the third argument for the MATCH function to find an exact match.

MATCH(Sheet2!B2, Sheet1!$B$2:$B$11, 0)

becomes

MATCH(1, {1; 2; 5; 7; 8; 10; 11; 13; 14; 15}, 0)

and returns 1.

Step 2 - Return value based on position

The INDEX function returns a value based ona a row number and a column number if needed.

INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2, Sheet1!$B$2:$B$11, 0))

becomes

INDEX(Sheet1!$A$2:$A$11,1)

becomes

INDEX({"customer 1"; "customer 2"; "customer 3"; "customer 4"; "customer 5"; "customer 6"; "customer 7"; "customer 8"; "customer 9"; "customer 10"},1)

and returns "customer 1" in cell A2.

Step 3 - Return blank if no value is found

The IFERROR function returns a blank value "" if the formula returns an error, this will return a blank cell if a number is missing.

The IFERROR function catches all kinds of errors in your formula, use with caution.

IFERROR(INDEX(Sheet1!$A$2:$A$11,MATCH(Sheet2!B2, Sheet1!$B$2:$B$11, 0)), "")

Get the Excel file

insert-new-rows-where-values-are-missing.xlsx

Missing values category

Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

What values are missing in List 1 that exists i List 2?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Identify missing numbers in two columns based on a numerical range
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

Identify missing three character alpha code numbers
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Highlight missing values between to columns
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

Functions in this article

IFERROR INDEX MATCH

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

3 Responses to “Insert blank rows for missing values”

HughMark says:

August 6, 2011 at 1:07 am

Hi Oscar,

Thanks for the reply and the time to help me on this one.
You are a big help.

God bless you always.

Reply

Rick Rothstein (MVP - Excel) says:

August 7, 2011 at 4:51 pm

If this operation needs to be done repeatedly, perhaps using a macro would be a more useful alternative...

Sub InsertMissingRows()
  Dim X As Long, LastRow As Long, Difference As Long
  Const OrderColumn As String = "B"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row
  For X = LastRow To StartRow + 1 Step -1
    Difference = Cells(X, OrderColumn).Value - Cells(X - 1, OrderColumn)
    If Difference > 1 Then
      Rows(X).Resize(Difference - 1).Insert
    End If
  Next
  LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row
  Cells(StartRow, OrderColumn).Resize(LastRow - StartRow + 1). _
        SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=1+R[-1]C"
  Columns("B").Value = Columns("B").Value
End Sub

Oscar says:

August 7, 2011 at 8:29 pm

HughMark,

You are welcome!

Rick Rothstein (MVP - Excel),

Thanks for your contribution!

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form