Excel Dates

[Closed]
Report
Posts
2
Registration date
Thursday November 8, 2012
Status
Member
Last seen
November 8, 2012
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
Hello,

I have two excel sheets.

In sheet one i have certain dates associated with certain IDs.

In sheet two i have certain dates associated with certain IDs.

I want to compare the IDs from both sheet and pick the data from sheet two for only dates which are before the date for sheet 1 for each ID.

Regards

1 reply

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
44
Hi Priyanka1987,

As per your post I created two excel worksheets. I add A1 in Sheet 1 as ID and B1 as Date, similarly I created another sheet. The data in the two sheets looks like this:

Sheet 1

ID01 1/2/2012
ID02 1/3/2012
ID03 1/4/2012
ID04 1/5/2012
ID05 1/6/2012
ID06 1/7/2012
ID07 1/8/2012
ID08 1/9/2012
ID09 1/10/2012
ID10 1/11/2012
ID11 1/12/2012
ID12 1/13/2012
ID13 1/14/2012

Sheet 2

ID14 1/2/2012
ID15 2/15/2012
ID16 2/16/2012
ID17 2/17/2012
ID18 2/18/2012
ID19 2/19/2012
ID20 2/20/2012
ID21 1/4/2012
ID22 2/22/2012
ID23 2/23/2012
ID24 1/14/2012
ID25 2/3/2012
ID26 2/4/2012

In column C2 of Sheet 2 I entered the below mentioned formula and used the fill handle to fill up to C14 in Sheet 2:

=IF(ISERROR(MATCH(Sheet2!B1,Sheet1!$B$1:$B$13,0)),"","SAME ID IN SHEET 01")

This formula will identify the same dates from both the sheets and will mark in column C of Sheet 2 against the date, "SAME ID IN SHEET 01", if the two dates are matching.The result will look like this:

ID14 1/2/2012 SAME ID IN SHEET 01
ID15 2/15/2012
ID16 2/16/2012
ID17 2/17/2012
ID18 2/18/2012
ID19 2/19/2012
ID20 2/20/2012
ID21 1/4/2012 SAME ID IN SHEET 01
ID22 2/22/2012
ID23 2/23/2012
ID24 1/14/2012 SAME ID IN SHEET 01
ID25 2/3/2012
ID26 2/4/2012

Please revert for clarification.