Excel 2010 Merging two documents and adding lines based on criteria

[Munkle555]

Hi I am not sure if this is the right subform but my query is this I have a list of extension numbers and in the next column I put =IF(A3-A2=1,"Not Free","Extension" & " " & A2 + 1 & " is free") but now what I want to do is merge a seperate spreadsheet that has the name of who is assigned to these extension numbers and add it the third column and once that is done I would to automaticly create a new line add in the name of the free extension and then replace the code in the second column with just free extension is that possible?

[Pete_UK]

You can use VLOOKUP or an INDEX/MATCH combination to get the name from the second sheet. Attach a sample workbook to show the way your data is laid out and to explain more clearly what you want to achieve.

Pete

[Munkle555]

Thank you for the reply. Ok on the attached the first sheet has a list of extensions with gaps which I used a code to find the gaps and the second sheet has extensions with names. Now these would be two seperate spreadsheets but for a example I have made them into one. So What I want to do is take those names from the second sheet and put them next to the matching number on the first. Then if you see the code in the second column of the first spreadsheet I want to make a new row take the extension from the code and placing it in column A of the new row and in column B of the new row put "Free extension". Hopefully that makes sense I am finding it difficult to explaine
example.xlsx

[Pete_UK]

You can use this formula in C2:

=IFERROR(VLOOKUP(A2,'List of Names'!A:C,3,0),"")

A formula can't insert a row, so you would need some macro to achieve that.

Hope this helps.

Pete

[Munkle555]

Thank you very much that worked like a charm

[Munkle555]

I found this code
Sub InsertRows()
Dim x As Long, y As Long
For x = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
y = Range("A" & x) - Range("A" & x - 1)
If y > 1 Then Range("A" & x).Resize(y - 1).EntireRow.Insert xlShiftDown
Next
End Sub
which inserts empty cells everytime it detects a gap do you know how I can modifiy it to add the number in?