# Filtering Question

#### thescream80

##### Board Regular
Hello - I am using Excel for Mac 2019 Version

I have 2 questions.

1. Is there a easy way for a noob like me to Filter 2 different areas on the same excel sheet?

I have created a section for New Completed Jobs and one for Total Completed jobs (seen below) and I am trying to Filter both in a descending order but I can only do that for 1 of them at a time. The info is coming from a new sheet that will update these numbers as they are added.

2. Is there a way to make the Sort filter dynamic or macro that would work with 2019 Mac edition? For example if someone completes 2 New jobs they would be moved to the top automatically without having to manually
sort?

Thank you for any help!

#### Sufiyan97

##### Well-known Member
Try

Book2
ABCDEFG
1NameNew Completed JobsTotal Completed JobsNameNew Completed JobsTotal Completed Jobs
2Ross24052400Rachel1621621
3Charlotte20071999Liz3141078
4Maya12431539Dean6262505
5Dave11232238Dave11232238
6Melynda11071573Maya12431539
8Dean6262505John50957
9Leah518511Charlotte20071999
10John50957Leah518511
12Liz3141078Ross24052400
13Joe2011397Matt9941571
14Joe2011397Day6791
15Rachel1621621Joe2011397
16Day6791thescream8020193
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1
Cell Formulas
RangeFormula
A2:A21A2=IFERROR(INDEX(E:E,MATCH(B2,F:F,0)),"")
B2:B21B2=IFERROR(LARGE(F:F,ROW()-1),"")
C2:C21C2=IFERROR(INDEX(G:G,MATCH(B2,F:F,0)),"")

Please note that it will not deal with duplicate value in New completed jobs like shown in cell F16 it will consider first value only

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Sufiyan97

##### Well-known Member
Ignore that and try below

Book1
ABCDEFG
1NameNew Completed JobsTotal Completed JobsNameNew Completed JobsTotal Completed Jobs
2Ross24052400Rachel1621621
3Charlotte20071999Liz3141078
4Maya12431539Dean6262505
5Dave11232238Dave11232238
6Melynda11071573Maya12431539
8Dean6262505John50957
9Leah518511Charlotte20071999
10John50957Leah518511
12Liz3141078Ross24052400
13B20145Matt9941571
14Ali20194Day6791
15thescream8020193Joe2011397
16Joe2011397thescream8020193
17Rachel1621621Ali20194
18Day6791B20145
19
20
21
22
23
24
25
Sheet1
Cell Formulas
RangeFormula
A2:A21A2=IFERROR(INDEX(E\$2:E\$500,AGGREGATE(15,6,(ROW(E\$2:E\$500)-ROW(E\$2)+1)/(F\$2:F\$500=LARGE(F\$2:F\$500,ROWS(A\$2:A2))),COUNTIF(F\$2:F\$500,">="&LARGE(F\$2:F\$500,ROWS(A\$2:A2)))-ROWS(A\$2:A2)+1)),"")
B2:B21B2=IFERROR(LARGE(F:F,ROW()-1),"")
C2:C21C2=IFERROR(INDEX(G\$2:G\$500,AGGREGATE(15,6,(ROW(G\$2:G\$500)-ROW(G\$2)+1)/(F\$2:F\$500=LARGE(F\$2:F\$500,ROWS(C\$2:C2))),COUNTIF(F\$2:F\$500,">="&LARGE(F\$2:F\$500,ROWS(C\$2:C2)))-ROWS(C\$2:C2)+1)),"")

#### thescream80

##### Board Regular
 =IFERROR(INDEX(G\$2:G\$500,AGGREGATE(15,6,(ROW(G\$2:G\$500)-ROW(G\$2)+1)/(F\$2:F\$500=LARGE(F\$2:F\$500,ROWS(C\$2:C2))),COUNTIF(F\$2:F\$500,">="&LARGE(F\$2:F\$500,ROWS(C\$2:C2)))-ROWS(C\$2:C2)+1)),"")
That worked PERFECTLY!!!! Thank you so much!!!!

#### Sufiyan97

##### Well-known Member
That worked PERFECTLY!!!! Thank you so much!!!!
You're Welcome

#### thescream80

##### Board Regular

You're Welcome

So I have another challenge for you.

So I needed to make another graph sorted the above my month total. So I have each month tabulating already but is there a way to sort them? I was thinking of sorting using the formula you had for New Completed jobs.

So for example it would go Rachael, David, Dean ect.... Each month will change so that is why I was thinking of using your other formula somehow

Here is what I have now

J2 Rachael is =IF(ISBLANK((DATA!B8001)),"",(DATA!B8001))
J3 Rachael is =IF(ISBLANK((DATA!B8002)),"",(DATA!B8002))
K2 Dean is = =IF(ISBLANK((DATA!V8001)),"",(DATA!V8001))
ect ect..

Thank you for any help on this!

#### thescream80

##### Board Regular
So I am looking to display them via the monthly number but sort the name via the highest total number. If that makes any sense

#### Sufiyan97

##### Well-known Member
So I am looking to display them via the monthly number but sort the name via the highest total number. If that makes any sense
Frankly speaking I don't have enough knowledge about chart and graphs

So ,it would be better that you start new thread with specifying your full requirement.

#### thescream80

##### Board Regular
Frankly speaking I don't have enough knowledge about chart and graphs

So ,it would be better that you start new thread with specifying your full requirement.
I fully Understand. I jsut thought you might be able to tackle it bc it would use your formula somehow. Thank you anyway for all of your help!

Replies
1
Views
134
Replies
0
Views
83
Replies
3
Views
33
Replies
15
Views
170
Replies
7
Views
60

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,110
Messages
5,768,150
Members
425,458
Latest member
Jaspal1996

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back