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

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
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)), "")
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?
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 19 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 [โฆ]
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 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!