Excel - sort data (smallest to largest) with 2 or more criteria in formula?

BasicDash

New Member
Joined
Nov 15, 2019
Messages
1
I am trying to find result of sort data in table with specific criteria
for example :
The picture of excel-table that i trying to figure it out

yellow is ALL-JOB fill by user
RED is Unfinish JOB sort by "START DATE"
Green is Completed JOB sort by "END DATE" and "JOB ID"

i need to sorting data in yellow table and show result in red/green table ,smallest to largest
Any help would be greatly appreciated. Thank you so much in advance.
ps.My English is not good. Please forgive me if I make grammatical mistakes.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
I recommend setting up ALL_JOB as a Table, see here:
https://support.office.com/en-us/ar...l-tables-7AB0BB7D-3A9E-4B56-A3C9-6C94334E492C

Select A2:D17 in ALL_JOB then select ribbon option Insert > Table. I also recommend you add another field to that table which is status. This will have a formula in cell E3 like this:
=if(len(d3)=0,"Unfinished","Completed")

Then I recommend you use pivot tables to create the finished jobs and unfinished jobs reports. Ask if you are unsure how to set up a pivot table or do a search on Google for resources. The pivot table allows you to pull through the fields you want and also you can also have a filter where you choose either the Unfinished or Completed jobs based on the status. Any time you want to see the status of various jobs you right click the pivot table, select Refresh and the pivot table will auto update itself based on the information you have in ALL_JOB.

P.S. I forgot to mention that you don't need the unfinished and completed sections given that information will appear in the pivot tables. Also, you can have two different pivot tables if you want, one for completed jobs and the other for unfinished jobs.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,162
Members
416,959
Latest member
Mohzein

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