[Calc] Help! Filtering a dated list with repeating items, excluding items that appear with later dates

[f300]

Hello,

I’ve got two long columns of dates and names. The dates and names can repeat.

01/06 Alpha
01/06 Bravo
01/06 Charlie
02/06 Delta
02/06 Alpha
03/06 Alpha
03/06 Echo
03/06 Echo

I want to input a date and create a list of names with only the names from the date I enter and earlier, excluding names that appear again with a later date.

Eg if I enter 01/06 I’d get:

Bravo
Charlie

But not Alpha as it also appears with a later date.

If I enter 02/06 I’d get:

Bravo
Charlie
Delta

I have no idea where to start with this so if someone could point me in the right direction I’d really appreciate it!
Thanks!

[mariosv]

Sample with pivot table.

• Goto A1
• Menu/Data/Pivot table - create - current selection
• drag Name to Row Fields
• In Results to, set up the address where you want filtered data, in this case $D$1
• Disable Total columns and Total rows.
• Ok
• Click on the filter and set up Date <= requiredDate

Pivot table IMHO is really a powerful tool.

[f300]

Thank you for your answer.

Unfortunately I think I need something more complicated than that. I need to exclude names that appear again in the list with a later date from the results.

Eg with the Pivot table filter set to <= 01/06/2014 I get:

Alpha
Bravo
Charlie

But I don’t want Alpha to be included as it appears again in the list with a later date.

[mariosv]

Then you need to go with the advanced filter: Menu/Data/Filter/Advanced filter. Filter: Applying Advanced Filters - LibreOffice Help