none
Find number and name of sales person RRS feed

  • Question

  • Hi, 

    I have a table, which lists the number of sales made by sales person for the year 2013. How do I list the number of sales and the name of sales person, whose sales are less than 4 for the year 2013.

    Name Gender Won Oppty Sales ( in Value USD) Sold Month and Year Year
    KFM WMM F 2 $1,279,310 72013 2013
    FSOR DPK M 2 $965,445 92013 2013
    JCS QJA M 3 $1,892,919 52013 2013
    YQFT NNOPI F 4 $2,205,713 102013 2013
    ZGZRS XSV M 4 $1,516,615 52013 2013
    AQOH KEYOZ M 4 $1,908,883 52013 2013
    DWE XSV M 5 $1,356,954 102013 2013
    YFE JDP F 6 $2,767,863 82013 2013
    KCRXF XSV F 6 $2,037,738 42013 2013
    OVEL LZS F 6 $2,276,738 72013 2013

    The answer I should get is

    2 KFM WMM
    2 FSOR DPK
    3 JCS QJA

    --

    Nirmal

    Sunday, January 18, 2015 3:24 PM

Answers

  • Let's say your table is in A1:F11.

    Somewhere to the right of the table, for example in H1, enter Won Oppty (spelled exactly as in the table). There should be at least one empty column between the table and this cell.

    In the cell below it (H2), enter <4

    In J1, enter Won Oppty and in K1, enter Name.

    Select cell A1.

    On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.

    Excel will select the entire table.

    Under Action, click Copy to another location.

    Click in the Criteria range box and select H1:H2.

    Click in the Copy to box and select J1:K1.

    Click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 18, 2015 7:14 PM
  • Hi~

    Formula cannot complete your needs.
    You can use criteria parameters (like "<4" and "=2013") in VBA code:


    Option Explicit
    
    Sub GetFilteredData()
        With ActiveSheet
            With .UsedRange
                .AutoFilter Field:=3, Criteria1:="<4"
                .AutoFilter Field:=6, Criteria1:="=2013"
            End With
            
            .Columns(2).Hidden = True
            .Columns("$D:$F").Hidden = True
        End With
    End Sub
    
    Sub RestoreData()
        With ActiveSheet
            If .AutoFilterMode Then
                .AutoFilterMode = False
            End If
            
            With .Columns(2)
                If .Hidden Then
                    .Hidden = False
                End If
            End With
            
            With .Columns("$D:$F")
                If .Hidden Then
                    .Hidden = False
                End If
            End With
        End With
    End Sub
    

    In Sheet1, insert two shapes and assign macro names to them, here's the result by clicking GetFilteredData and RestoreData:

    Regards,
    Cristin Yan
    Monday, January 19, 2015 4:03 AM

All replies

  • Let's say your table is in A1:F11.

    Somewhere to the right of the table, for example in H1, enter Won Oppty (spelled exactly as in the table). There should be at least one empty column between the table and this cell.

    In the cell below it (H2), enter <4

    In J1, enter Won Oppty and in K1, enter Name.

    Select cell A1.

    On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.

    Excel will select the entire table.

    Under Action, click Copy to another location.

    Click in the Criteria range box and select H1:H2.

    Click in the Copy to box and select J1:K1.

    Click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 18, 2015 7:14 PM
  • Thank you for your reply. But I need to have the answer via formula :(

    --

    NG

    Monday, January 19, 2015 2:22 AM
  • Hi~

    Formula cannot complete your needs.
    You can use criteria parameters (like "<4" and "=2013") in VBA code:


    Option Explicit
    
    Sub GetFilteredData()
        With ActiveSheet
            With .UsedRange
                .AutoFilter Field:=3, Criteria1:="<4"
                .AutoFilter Field:=6, Criteria1:="=2013"
            End With
            
            .Columns(2).Hidden = True
            .Columns("$D:$F").Hidden = True
        End With
    End Sub
    
    Sub RestoreData()
        With ActiveSheet
            If .AutoFilterMode Then
                .AutoFilterMode = False
            End If
            
            With .Columns(2)
                If .Hidden Then
                    .Hidden = False
                End If
            End With
            
            With .Columns("$D:$F")
                If .Hidden Then
                    .Hidden = False
                End If
            End With
        End With
    End Sub
    

    In Sheet1, insert two shapes and assign macro names to them, here's the result by clicking GetFilteredData and RestoreData:

    Regards,
    Cristin Yan
    Monday, January 19, 2015 4:03 AM