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
=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.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
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.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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
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?
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?
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
=(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
You mean?
=AND((MIN(IF(Sheet2!$A$1:$A$3788=A2,Sheet2!$B$1:$B$3788))-B2)>=30,(MIN(IF(Sheet2!$A$1:$A$3788=A2,Sheet2!$B$1:$B$3788))-B2)<=30))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks