Sort Date Descending in New Sheet within Row

Dear Experts
I have very Big Data varying from time to time (contains Two rows of Data for each record)
1. The column "F" (Date) to end of each Row has to be sorted latest to old (z-a) leaving blank cells along with the remarks Down there
2. If the Date in the F Column is less than 'a1' cell (Current Date / Date We mention) the both rows shall become red

The result has to be in Report sheet as in sample file

Thanks
Jai

Hi,

You need to first change the dates in Sub sheet in date format as they all are in General format.

Now in final sheet F3=IFERROR(INDEX(Sub!$F3:$BJ3,,LARGE(COUNTIFS(Sub!$F3:$BJ3,">="&Sub!$F3:$BJ3),COLUMNS(Final!$E3:E3))),"") put this formula with CTRL+SHIFT+ENTER and drag over.

F3

Formula:

Please Login or Register  to view this content.

Now in final sheet F4=IFERROR(IF(INDEX(Sub!$F4:$BJ4,,MATCH(F3,Sub!$F3:$BJ3,0))=0,"",INDEX(Sub!$F4:$BJ4,,MATCH(F3,Sub!$F3:$BJ3,0))),"") and drag over.

F4

Formula:

Please Login or Register  to view this content.

For the conditional formatting you need to put formula =$F3>$A$1 and format as you need.

You file is attached.

Hope this will help you.

Thx
Mr.Ankur Shukla
for the immediate answer

Still I have the following problem
Row No 7 originally contain 4 Dates, the resultant sheet gives more dates

Shall I have to copy and paste every time i get new data or i resort to VBA

Jai

Thanks to adopted and modified Code of Mr.Jindon

Please Login or Register  to view this content.

It work like Gem

Now the problem is
Each data is a set of two rows. 1st Cell is Date and 2nd Cell (just under Date) is Remarks of the Date
So, the Sorting has to be done two rows together like F3&F4, G3&G4, H3&H4.... F5&F6, G5&G6, H%&H6...
I tried to change "For i = 1" to "For i = 2"
But it does not work

Jai