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]

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?

Thank you for any help!

You can sort both of them in descending order, for that you need to insert a blank column in between them and then remove filter and then reapply filter

like below then you can remove the blank column

Book2
	A	B	C
1	No oF Completed Jobs		Total Completed Jobs
2	1		2473
3	0		2394
4	0		2361
5	0		2198
6	0		1555
7	0		1554
8	0		1533
9	0		1513
10	0		1391
11	0		239
Sheet1

 

[Sufiyan97]

or try below formula it will update automatically when you insert new numbers below

Book2
	A	B	C	D	E
1	No oF Completed Jobs	Total Completed Jobs		Decending Order	Decending Order
2	0	1533		1	2473
3	0	1513		0	2394
4	0	239		0	2361
5	0	1554		0	2198
6	0	2473		0	1555
7	0	2394		0	1554
8	0	2361		0	1533
9	0	2198		0	1513
10	1	1555		0	1391
11	0	1391		0	239
12
13
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
D2:E20	D2	=IFERROR(LARGE(A:A,ROW()-1),"")

 

[thescream80]

Thank you for you r help on that @ADurfani

The only issue that is coming up on the sorting formula is in column A - I have a list of names that teh new completed and total completed jobs corresponds with. Is there a way to use that formula but have it move column A and B so the names match the numbers?

 

[Sufiyan97]

Is it what you want?

But remember for duplicate values it is taking first name for both numbers so we need to wait until another super user enters to the thread

Book1
	A	B	C	D	E
1	No oF Completed Jobs	Total Completed Jobs		Decending Order	Decending Order
2	A	2361		I	2473
3	B	2198		I	2473
4	C	1555		J	2394
5	D	1391		A	2361
6	E	1533		A	2361
7	F	2361		B	2198
8	G	239		C	1555
9	H	1554		H	1554
10	I	2473		E	1533
11	J	2394		D	1391
12	K	2473		G	239
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=IFERROR(INDEX(A:A,MATCH(E2,B:B,0)),"")
E2:E12	E2	=IFERROR(LARGE(B:B,ROW()-1),"")

 

[thescream80]

Is it what you want?

But remember for duplicate values it is taking first name for both numbers so we need to wait until another super user enters to the thread

Book1
	A	B	C	D	E
1	No oF Completed Jobs	Total Completed Jobs		Decending Order	Decending Order
2	A	2361		I	2473
3	B	2198		I	2473
4	C	1555		J	2394
5	D	1391		A	2361
6	E	1533		A	2361
7	F	2361		B	2198
8	G	239		C	1555
9	H	1554		H	1554
10	I	2473		E	1533
11	J	2394		D	1391
12	K	2473		G	239
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=IFERROR(INDEX(A:A,MATCH(E2,B:B,0)),"")
E2:E12	E2	=IFERROR(LARGE(B:B,ROW()-1),"")

I see what you are saying here... So if i want to sort JUST the number of completed jobs to their name and not worry about auto sorting total completed jobs. What would I do?

 

[Sufiyan97]

I see what you are saying here... So if i want to sort JUST the number of completed jobs to their name and not worry about auto sorting total completed jobs. What would I do?

did not get your question.

 

[thescream80]

did not get your question.

A	B
Name	Completed Jobs
John	1
Susan	2
Tim	0
Mike	5

Using this Column A and Column B how can I use your format to auto sorts column b with still keeping the correct name in column A? I will be updating numbers from a different tab so column b # will be alwasy be changing.

Thank you for your help. NOOB on this stuff.

 

[Sufiyan97]

A	B
Name	Completed Jobs
John	1
Susan	2
Tim	0
Mike	5

Using this Column A and Column B how can I use your format to auto sorts column b with still keeping the correct name in column A? I will be updating numbers from a different tab so column b # will be alwasy be changing.

Thank you for your help. NOOB on this stuff.

did you use formula in post #5?
It should work.

 

[thescream80]

No I could not get that to work - Here is what I am working with. Column E-G is going to be getting the info from another sheet. In fact I will keep that hidden.

Column A-C will read E-G.

Column B is the column that I am trying to have it auto sort so when I entered new info in the other sheet it will sort automatically from highest to lowest WHILE keeping the correct name and total completed jobs.

In the end I would like to to look like this.