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: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
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.
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...
Sub InsertMissingRows() Dim X As Long, LastRow As Long, Difference As Long Const OrderColumn As String = "B" Const StartRow As Long = 2 LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row For X = LastRow To StartRow + 1 Step -1 Difference = Cells(X, OrderColumn).Value - Cells(X - 1, OrderColumn) If Difference > 1 Then Rows(X).Resize(Difference - 1).Insert End If Next LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row Cells(StartRow, OrderColumn).Resize(LastRow - StartRow + 1). _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=1+R[-1]C" Columns("B").Value = Columns("B").Value End SubHughMark,
You are welcome!
Rick Rothstein (MVP - Excel),
Thanks for your contribution!