Insert rows for missing values in excel
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
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.
Download excel file
insert new rows where values are missing.xlsx
Excel 2007 *.xlsx
Related posts:
Insert a new row at the top every time a value has been entered
Merge matching rows in excel (text values)
Insert hyperlinks to all files in current folder
Recommended categories
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!