Filtering Question

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
111
Office Version
  1. 2016
Platform
  1. MacOS
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.


Screen Shot 2021-03-12 at 8.52.34 AM.png




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
Joined
Apr 12, 2019
Messages
638
Office Version
  1. 2013
Platform
  1. Windows
Try

Book2
ABCDEFG
1NameNew Completed JobsTotal Completed JobsNameNew Completed JobsTotal Completed Jobs
2Ross24052400Rachel1621621
3Charlotte20071999Liz3141078
4Maya12431539Dean6262505
5Dave11232238Dave11232238
6Melynda11071573Maya12431539
7Matt9941571Brad4662422
8Dean6262505John50957
9Leah518511Charlotte20071999
10John50957Leah518511
11Brad4662422Melynda11071573
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

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

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
638
Office Version
  1. 2013
Platform
  1. Windows
Ignore that and try below

Book1
ABCDEFG
1NameNew Completed JobsTotal Completed JobsNameNew Completed JobsTotal Completed Jobs
2Ross24052400Rachel1621621
3Charlotte20071999Liz3141078
4Maya12431539Dean6262505
5Dave11232238Dave11232238
6Melynda11071573Maya12431539
7Matt9941571Brad4662422
8Dean6262505John50957
9Leah518511Charlotte20071999
10John50957Leah518511
11Brad4662422Melynda11071573
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)),"")
 
Solution

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
111
Office Version
  1. 2016
Platform
  1. MacOS
=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!!!!
 

thescream80

Board Regular
Joined
Mar 28, 2014
Messages
111
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

You're Welcome

Glad to help!
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.
Screen Shot 2021-05-05 at 3.59.07 PM.png


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

Screen Shot 2021-05-05 at 4.03.11 PM.png

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
Joined
Mar 28, 2014
Messages
111
Office Version
  1. 2016
Platform
  1. MacOS
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
Joined
Apr 12, 2019
Messages
638
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Mar 28, 2014
Messages
111
Office Version
  1. 2016
Platform
  1. MacOS
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!
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top