## Insert blank rows for missing values

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:

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.

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)), "")

### Missing values category

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

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

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

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

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

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

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 3 Responses to “Insert blank rows for missing values”

### Leave a 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

Hi Oscar,

Thanks for the reply and the time to help me on this one.

You are a big help.

God bless you always.

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

HughMark,

You are welcome!

Rick Rothstein (MVP - Excel),

Thanks for your contribution!