Ask Your Question

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

asked 2014-05-06 00:42:26 +0100

f300 gravatar image


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:


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

If I enter 02/06 I'd get:


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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 21:17:18.330963

1 Answer

Sort by » oldest newest most voted

answered 2014-05-06 03:41:53 +0100

m.a.riosv gravatar image

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.

Pivot table

edit flag offensive delete link more


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:


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

f300 gravatar imagef300 ( 2014-05-23 13:04:02 +0100 )edit

Then you need to go with the advanced filter: Menu/Data/Filter/Advanced filter.

m.a.riosv gravatar imagem.a.riosv ( 2014-05-23 21:03:27 +0100 )edit

Question Tools

1 follower


Asked: 2014-05-06 00:42:26 +0100

Seen: 191 times

Last updated: May 06 '14