## Insert 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:**

I am not sure this is the most efficient solution.

**Create new OR numbers on a new sheet**

- Type 1 in cell B2.
- Select cell B2
- Right click and hold on black dot on cell B2.

- Drag down to cell B16.

- Click "Fill series"

**Match OR number and return customer name**

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.

**Download excel file**

insert new rows where values are missing.xlsx

Excel 2007 *.xlsx

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

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 missing dates in a set of date ranges

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

How to calculate missing months in a given date range

Question: I have dates in a list. I would like to know how to identify missing months in this list […]

Find missing numbers in a range containing multiple columns

Question: I have numbers spanning over multiple columns. I want to know the smallest value and the largest value and […]

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? […]

### 3 Responses to “Insert 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

Use the img tag, like this: <img src="Insert pic link here">

**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!