On Sheet1, add 4 hidden columns, where you calculate the order number of project in given status. P.e.
1st hidden column (E) for projects In Prog has formulas returning values
"",1,"","","",2,"","","",3,"","","", 4,"","","",5
2nd hidden column (F) for projects Pending has formulas returning values
"","","",1,"","","",2,"","","",3,"","","",4,"" ,""
3rd hidden column (G) for projects Completed has formulas returning values
"",1,"","","",2,"","","",3,"","","", 4,"","","",5
4th hidden column (H] for projects Not Assigned has formulas returning values
"","","",1,"","","",2,"","","",3,"","","",4,"" ,""

Now you create 4 named values, p.e. CntInProg, CntPending, CntCompleted and CntNA, which calculate the MAX values in corresponding hidden columns.

On Sheet2, p.e. into cell A2 you enter the formula like
=IF(ROW()-ROW($A$1>CntInPrg,"",INDEX(Sheet1!$A$6:$A$23,MATCH (ROW()-ROW($A$1),Sheet1!$E$6:$E$23,0))), and copy down for as much rows as you need.

Continue same for other statuses with next 3 columns.