I have an excel file which have two worksheet named contact and newnumber. In contact…
- User
I have an excel file which have two worksheet named contact and newnumber. In contact sheet i have name, phonenumber and asignee. i newphonenumber I just have name and phonenumber. I want a macro that create a new worksheet and give me all newphoenumber list with their asignee, if exist. and i also want to sort these numbers according to asignee name.
- Assistant
You can use the following VBA code to create a macro that does what you need. Press ALT + F11 to open Visual Basic for Applications (VBA) editor, then click on "Insert" and select "Module". Paste the code below and save the workbook as an Excel Macro-Enabled Workbook (.xlsm format):
Sub GetNewNumbersWithAsignees() Dim wsContact As Worksheet, wsNewNumber As Worksheet, wsResult As Worksheet Dim contactList As Range, newNumberList As Range, result As Range Dim name As Variant, number As Variant, asignee As Variant Dim r As Long Dim dic As Object, key As Variant ' Set references to the sheets in the workbook Set wsContact = ThisWorkbook.Worksheets("contact") Set wsNewNumber = ThisWorkbook.Worksheets("newnumber") ' Create a new worksheet for results Set wsResult = ThisWorkbook.Worksheets.Add(Before:=Sheets(1)) wsResult.Name = "Result" ' Set references to the data ranges Set contactList = wsContact.Range("A2:C" & wsContact.Cells(wsContact.Rows.Count, 1).End(xlUp).Row) Set newNumberList = wsNewNumber.Range("A2:B" & wsNewNumber.Cells(wsNewNumber.Rows.Count, 1).End(xlUp).Row) wsResult.Cells(1, 1).Value = "Name" wsResult.Cells(1, 2).Value = "New Number" wsResult.Cells(1, 3).Value = "Asignee" Set result = wsResult.Range("A2:C2") Set dic = CreateObject("Scripting.Dictionary") ' Populate dictionary with the contact sheet data For Each contactRow In contactList.Rows name = contactRow.Cells(1, 1).Value asignee = contactRow.Cells(1, 3).Value If Not dic.Exists(name) Then dic.Add name, asignee End If Next contactRow r = 1 ' Get the new numbers with their asignees For Each newNumberRow In newNumberList.Rows name = newNumberRow.Cells(1, 1).Value number = newNumberRow.Cells(1, 2).Value If dic.Exists(name) Then asignee = dic(name) result.Cells(r, 1).Value = name result.Cells(r, 2).Value = number result.Cells(r, 3).Value = asignee r = r + 1 End If Next newNumberRow ' Sort the result based on asignee name wsResult.Sort.SortFields.Clear wsResult.Sort.SortFields.Add2 Key:=Range("C3:C" & r), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With wsResult.Sort .SetRange Range("A2:C" & r) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubAfter pasting the code, just close the VBA editor and go back to your workbook. Press ALT + F8 to open the Macro dialog, select "GetNewNumbersWithAsignees" and click "Run". This will create a new worksheet with the desired results, sorted by asignee name.