extract records for the last entry date for the month

  • Thread starter Thread starter Tee Lin Koon
  • Start date Start date
T

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).
 
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).
Click to expand...


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 D2:D13

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
 
Back
Top