# Find number and name of sales person

• ### 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

• 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~

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

--

NG

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

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