Skip to content
The Chat Archive
gpt-4-0314· OpenAI WildChat-4.8M (AI2)

I have an excel file which have two worksheet named contact and newnumber. In contact…

  1. 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.

  2. 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 Sub
    

    After 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.