Comparing two sets of data

**redrasta** · 02-13-2012, 08:19 AM

Hi,

I have two sets of data I need to compare and I need put conditions to find out where an argument is true between the two sets.

For example:

Dataset 1, column A1 e-mail (E-mail1), column B1 date (Date1)
Dataset 2, column A2 e-mail (E-mail2), column B2 date (Date2)

I need to lookup E-mail 1 in column A2, if E-mail1 exists in A2 then I need to check that Date1 is within 30 days of Date 2.

Hope that makes sense.

Thanks,
Anthony

**NBVC** · 02-13-2012, 08:33 AM

=ABS(SUMIF('Sheet2'!A:A,A2,'Sheet2'!B:B)-B2)<=30

This assumes one match, if any, in dataset2 (sheet2).

This finds the date in Sheet2 column B for item in Column A that matches A2 in current sheet, and subtracts from B2's date. If the Absolute value of that is less than or equal to 30, then TRUE is returned, which means the date difference is less than or equal to 30 days either way, if you need only one way, then remove the ABS() function and rearrange the SUMIF and B2 arguments, if necessary.

**Fotis1991** · 02-13-2012, 08:40 AM

Hi

Maybe something like this in C2.

=IF(AND(A1=A2;B2<=(B1+30));"ok";"no")

Change the semi-colons to gomma, if you have to do it.

**redrasta** · 02-13-2012, 09:35 AM

In some cases there may be more than one match. This is an awesome response for the problem but is there anyway to widen the search to the closest possible match for date1?

Thanks,
Anthony

**NBVC** · 02-13-2012, 09:47 AM

Maybe:

=ABS(MIN(IF('Sheet2'!$A$1:$A$100=A2,'Sheet2'!$B$1:$B$100))-B2)<=30

confirmed with CTRL+SHIFT+ENTER not just ENTER

This checks the MIN date in A1:A100 against the date in B2... is this okay or does it need further refining?

**redrasta** · 02-13-2012, 11:18 AM

But the sumif was checking that the data was there (i.e. picking up the data corresponding to the e-mail) does the "min" not just take the smallest overall value from sheet 2 wether or not it is related to the same e-mail?

**NBVC** · 02-13-2012, 11:26 AM

The MIN(IF()) is still basing it on the condition that 'Sheet2'!$A$1:$A$100=A2 and returns the min (oldest) date... maybe it should be MAX(IF()) to find latest date??

We can add the Sumif, actuall Countif will do... to check for match also...

=IF(COUNTIF('Sheet2'!$A$1:$A$100,A2)=0,"no match found",ABS(MAX(IF('Sheet2'!$A$1:$A$100=A2,'Sheet2'!$B$1:$B$100))-B2)<=30)

confirmed with CTRL+SHIFT+ENTER

**redrasta** · 02-16-2012, 06:44 AM

=(MIN(IF(Sheet2!$A$1:$A$3788=A2,Sheet2!$B$1:$B$3788))-B2)<=30

This formula seems to work best however I'm getting some anomalies where the results is lees than zero. Is there a way of saying <=30 but >=0?

Thanks,
Anthony