Copy data from one table to another ordered by date

[matt0020190]

Hi everyone,

Hope you are well.

I get by in excel and able to use the most formulas for my needs. However this one has stumped me.

I have a table with employee details within. I basically want to populate another table on another sheet automatically via formula for those employees who have left. Now I have the below formula that works well with one issue.... it orders the new leavers table by the order of the primary table. I want the leavers table ordered by the date they left and not the date the rows appear in the original table.

Here is my current formula in the leavers table which works but does not order by date the employee left.

=IFERROR(INDEX('Area Master Data'!$A:$A,SMALL(IF('Area Master Data'!B10:B193<>"",ROW('Area Master Data'!B10:B193),FALSE),ROW()-10)),"")

My original table looks like this:

A -------------------------- B ---------------------------

Emp Number ------------- Leaver Date -----------------

1234 --------------------- ------------------------------

1365----------------------- 10/10/2020 ---------------------

4565------------------------ 23/10/2020 --------------------

6571----------------------------------------------------------

6661------------------------01/01/2020 ---------------------

4561----------------------- 09/09/2020 ---------------------

Leavers table (what I want it to display like - a list ordered by the date they left):

A--------------------- B----------------------------------

Emp Number -------- Leave Date ----------------------

6661------------------01/01/2020-----------------------

4561------------------09/09/2020-----------------------

1365 ------------------10/10/2020 ---------------------

4565 ------------------23/10/2020 ---------------------

Anyone got any ideas please?

PS - I know VBA code would do an auto sort or something, but security policies at my workplace prevent macros. Therefore I need formula only

[#2]

@matt0020190 I suggest you look into Pivot Tables. No complicated formulae, no macros. Attached a simple example based on your sample data.

[#3]

@matt0020190 

As variant

=FILTER(A1:INDEX(B:B,COUNTA(A:A)),B1:INDEX(B:B,COUNTA(A:A))<>"")

[#4]

Thanks - saw this but only wanted to avoid due to the formatting aspect that doesnt suit my table destination. However will have a play and see what works

[#5]

Thanks - this is the type of formula I was looking for - however it actually only delivers the same as my own formula above. Your example does not filter the table by date the employee left. I would have to use a macro to autofilter or manually sort. Am I missing something?

[#6]

@matt0020190 

If with sorting that could be

=LET(
  range, A1:INDEX(B:B,COUNTA(A:A)),
  allLeaveDates, B1:INDEX(B:B,COUNTA(A:A)),
  rangeWithLeaves, FILTER(range,allLeaveDates<>""),
  dates, INDEX(rangeWithLeaves,0,2),
  sortedRange, SORTBY(rangeWithLeaves,dates,-1),
 sortedRange)