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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top