Filtering Question

[thescream80]

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]

Try

Book2
	A	B	C	D	E	F	G
1	Name	New Completed Jobs	Total Completed Jobs		Name	New Completed Jobs	Total Completed Jobs
2	Ross	2405	2400		Rachel	162	1621
3	Charlotte	2007	1999		Liz	314	1078
4	Maya	1243	1539		Dean	626	2505
5	Dave	1123	2238		Dave	1123	2238
6	Melynda	1107	1573		Maya	1243	1539
7	Matt	994	1571		Brad	466	2422
8	Dean	626	2505		John	509	57
9	Leah	518	511		Charlotte	2007	1999
10	John	509	57		Leah	518	511
11	Brad	466	2422		Melynda	1107	1573
12	Liz	314	1078		Ross	2405	2400
13	Joe	201	1397		Matt	994	1571
14	Joe	201	1397		Day	67	91
15	Rachel	162	1621		Joe	201	1397
16	Day	67	91		thescream80	201	93
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
A2:A21	A2	=IFERROR(INDEX(E:E,MATCH(B2,F:F,0)),"")
B2:B21	B2	=IFERROR(LARGE(F:F,ROW()-1),"")
C2:C21	C2	=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

 

[Sufiyan97]

Ignore that and try below

Book1
	A	B	C	D	E	F	G
1	Name	New Completed Jobs	Total Completed Jobs		Name	New Completed Jobs	Total Completed Jobs
2	Ross	2405	2400		Rachel	162	1621
3	Charlotte	2007	1999		Liz	314	1078
4	Maya	1243	1539		Dean	626	2505
5	Dave	1123	2238		Dave	1123	2238
6	Melynda	1107	1573		Maya	1243	1539
7	Matt	994	1571		Brad	466	2422
8	Dean	626	2505		John	509	57
9	Leah	518	511		Charlotte	2007	1999
10	John	509	57		Leah	518	511
11	Brad	466	2422		Melynda	1107	1573
12	Liz	314	1078		Ross	2405	2400
13	B	201	45		Matt	994	1571
14	Ali	201	94		Day	67	91
15	thescream80	201	93		Joe	201	1397
16	Joe	201	1397		thescream80	201	93
17	Rachel	162	1621		Ali	201	94
18	Day	67	91		B	201	45
19
20
21
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
A2:A21	A2	=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:B21	B2	=IFERROR(LARGE(F:F,ROW()-1),"")
C2:C21	C2	=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]

=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]

That worked PERFECTLY!!!! Thank you so much!!!!

You're Welcome

Glad to help!

 

[thescream80]

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.

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]

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]

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]

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!