T extract older dates in a column

Muthukrishnan V

Active Member
Joined
May 29, 2008
Messages
269
Office Version
  1. 365
Platform
  1. Windows
Excel 365

Dates less than 01-04-2015 in a column

Column D5:D964 contains dates in DD-MM-YYYY format
and a few cells in this column are blank also.

My requirement: I want formula to show Cell Reference
and the relative date,
separately elsewhere in the
same worksheet, when the date is <01-04-2015.

Thanking you,
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This will give you the first instance in the column:
mr excel questions 12.xlsm
ABCD
1DateFiltered
201-03-201401-12-2013$A$25
301-05-201501-02-2014$A$13
401-04-201501-03-2014$A$2
501-04-201501-06-2014$A$22
601-07-2014$A$17
701-09-2014$A$11
801-10-2014$A$26
901-01-2015$A$12
1001-04-201501-02-2015$A$16
1101-09-201401-04-2015$A$4
1201-01-2015
1301-02-2014
1401-02-2014
1501-05-2015
1601-02-2015
1701-07-2014
18
19
20
21
2201-06-2014
2301-06-2014
2401-01-2015
2501-12-2013
2601-10-2014
Sheet33
Cell Formulas
RangeFormula
C2:C11C2=UNIQUE(SORT(FILTER(A2:A26,((A2:A26)<=DATE(2015,4,1))*((A2:A26)<>""),"")))
D2:D11D2=CELL("address",OFFSET($A$1,MATCH(C2,$A$2:$A$26,0),0))
Dynamic array formulas.
 
Upvote 0
A couple of other options to return the cell address that don't use the volatile OFFSET function.
If you could have duplicate dates as in @awoohaw example then the formula below will return all addresses.
If you will not have duplicate dates then try:
=ADDRESS(MATCH($C2,$A$1:$A$26,0),1,1)

Book1
ABCDEF
1DateFilteredAddress
21/03/20141/12/2013$A$25  
31/05/20151/02/2014$A$13$A$14 
41/04/20151/03/2014$A$2  
51/04/20151/06/2014$A$22$A$23 
61/07/2014$A$17  
71/09/2014$A$11  
81/10/2014$A$26  
91/01/2015$A$12$A$24 
101/04/20151/02/2015$A$16  
111/09/20141/04/2015$A$4$A$5$A$10
121/01/2015
131/02/2014
141/02/2014
151/05/2015
161/02/2015
171/07/2014
18
19
20
21
221/6/2014
231/6/2014
241/1/2015
251/12/2013
261/10/2014
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=UNIQUE(SORT(FILTER(A2:A26,((A2:A26)<=DATE(2015,4,1))*((A2:A26)<>""),"")))
D2:F11D2=IF(COUNTIF($A$2:$A$26,$C2)>=COLUMNS($D$2:D$2),ADDRESS(AGGREGATE(15,6,(ROW($A$2:$A$26))/($A$2:$A$26=$C2),COLUMNS($D$2:D2)),1,1),"")
Dynamic array formulas.
 
Upvote 0
Thank you Mr Awoohaw sir and Mr AhoyNC sir. Both formulas work great.
I am grateful to the Forum.
 
Upvote 0
No mention of duplicates so I will assume none for now or if duplicates, reporting one will suffice. But since the dates are "spilled", the addresses could be too & in the same formula.

A couple of choices depending on if HSTACK is available or not.

23 03 06.xlsm
ABCDEFG
1DateFilteredAddressFilteredAddress
201-03-201401-12-2013A2501-12-2013A25
301-05-201501-02-2014A1301-02-2014A13
401-04-201502-02-2014A1402-02-2014A14
501-04-201501-03-2014A201-03-2014A2
601-06-2014A2201-06-2014A22
727-06-2014A2327-06-2014A23
801-07-2014A1701-07-2014A17
901-09-2014A1101-09-2014A11
1001-04-201501-10-2014A2601-10-2014A26
1101-09-201401-01-2015A2401-01-2015A24
1215-01-201515-01-2015A1215-01-2015A12
1301-02-201401-02-2015A1601-02-2015A16
1402-02-2014
1501-05-2015
1601-02-2015
1701-07-2014
18
19
20
21
2201-06-2014
2327-06-2014
2401-01-2015
2501-12-2013
2601-10-2014
27
Date & address
Cell Formulas
RangeFormula
C2:D13C2=LET(d,UNIQUE(SORT(FILTER(A2:A26,(A2:A26<DATE(2015,4,1))*(A2:A26<>""),""))),HSTACK(d,ADDRESS(MATCH(d,A:A,0),COLUMN(A1),4)))
F2:G13F2=LET(d,UNIQUE(SORT(FILTER(A2:A26,(A2:A26<DATE(2015,4,1))*(A2:A26<>""),""))),CHOOSE({1,2},d,ADDRESS(MATCH(d,A:A,0),COLUMN(A1),4)))
Dynamic array formulas.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top