Author: Oscar Cronquist Article last updated on September 20, 2017

HughMark asks: 

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?

(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


I am not sure this is the most efficient solution.

Create new OR numbers on a new sheet

  1. Type 1 in cell B2.
  2. Select cell B2
  3. Right click and hold on black dot on cell B2.
  4. Drag down to cell B16.
  5. Click "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.







Download excel file

insert new rows where values are missing.xlsx
Excel 2007 *.xlsx