How delete the oldest dates from the sheet

[Romano_odK]

Good morning,

In the column E "Geldig tot" there are dates. If you look at column A you see the same item codes (Artikelcode). Now I need to see only the newest date, so only one item code with the newest date. Is that possible to filter out and delete the rest?

Thank you for your time.

Book1
	A	B	C	D	E
1	Artikelcode	Omschrijving	Eenheid	Geldig van	Geldig tot
2	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	01/07/2020	11/04/2021
3	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	12/04/2021	21/04/2021
4	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	22/04/2021	30/06/2021
5	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	01/07/2021	14/10/2021
6	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	15/10/2021	02/01/2022
7	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	03/01/2022	31/01/2022
8	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	01/02/2022	30/05/2022
9	100000	Vito Glaserfix 111 6x2 mm wit - 10x25 m	pak	31/05/2022	31/12/2299
10	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	01/07/2020	11/04/2021
11	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	12/04/2021	22/04/2021
12	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	23/04/2021	30/06/2021
13	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	01/07/2021	14/10/2021
14	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	15/10/2021	02/01/2022
15	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	03/01/2022	31/01/2022
16	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	01/02/2022	30/05/2022
17	100001	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	pak	31/05/2022	31/12/2299
18	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	01/07/2020	11/04/2021
19	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	12/04/2021	30/06/2021
20	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	01/07/2021	14/10/2021
21	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	15/10/2021	02/01/2022
22	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	03/01/2022	31/01/2022
23	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	01/02/2022	30/05/2022
24	100002	Vito Glaserfix 111 6x3 mm wit - 10x25 m	pak	31/05/2022	31/12/2299
25	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	01/07/2020	30/06/2021
26	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	01/07/2021	14/10/2021
27	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	15/10/2021	02/01/2022
28	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	03/01/2022	31/01/2022
29	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	01/02/2022	30/05/2022
30	100003	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	pak	31/05/2022	31/12/2299
Query1

 

[Legacy 143009]

In a separate column, let say F2:

=IFERROR(INDEX($A$2:$A$30,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$30),0)),"")

G2:

=VLOOKUP($F2,$A$2:$B$30,2,0)

H2:

=VLOOKUP($F2,$A$2:$C$30,3,0)

J2:

=MAXIFS($E$2:$E$30,$A$2:$A$30,$F2)

I2:

=INDEX($D$2:$D$30,MATCH(1,($A$2:$A$30=$F2)*($E$2:$E$30=$J2),0))

 

[rlv01]

Is that possible to filter out and delete the rest?

You did not mention what kind of solution you are after. Do you just want to know how to do it via menu commands (i.e. Sort table by Col A (ascending) & Col E (descending), then use the 'remove duplicates' feature to remove the duplicates in Col A) or are you wanting to automate is some way?

 

[Romano_odK]

In a separate column, let say F2:

=IFERROR(INDEX($A$2:$A$30,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$30),0)),"")

G2:

=VLOOKUP($F2,$A$2:$B$30,2,0)

H2:

=VLOOKUP($F2,$A$2:$C$30,3,0)

J2:

=MAXIFS($E$2:$E$30,$A$2:$A$30,$F2)

I2:

=INDEX($D$2:$D$30,MATCH(1,($A$2:$A$30=$F2)*($E$2:$E$30=$J2),0))

Thank you for your answers, this does the trick I needed.
Have a great day,

Romano

 

[Legacy 143009]

Thanks for the feedback