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

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?

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

  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.

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

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!