extract records for the last entry date for the month

[Tee Lin Koon]

I have few thousands of data with date in one of the columns.
I want to extract unique records for the last day of the my data date
for every month (not end of month , sometimes it can be 28 of January
or 26 of February).

 

[Don Guillett]

data>filter>autofilter

 

[Ron Rosenfeld]

I have few thousands of data with date in one of the columns.
I want to extract unique records for the last day of the my data date
for every month (not end of month , sometimes it can be 28 of January
or 26 of February).

Hard to tell without knowing more about your data configuration.

Assume a table of this general configuration named Tbl:

Date Data
2-Jan 9
5-Jan 10
26-Jan 10
1-Feb 8
15-Feb 3
28-Feb 10
3-Mar 6
15-Mar 4
31-Mar 4


Also assume that there is only one entry on the last day of the month.

Set up another column like so in D213

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

(These are Text entries, not formatted dates)

In E2, *array-enter* the formula:

=VLOOKUP(MAX((TEXT(Date,"mmm")=D2)*Date),Tbl,2)

Copy/drag this down to E13.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron