Counting Unique differences between two worksheets

[D-Spark]

I have the following formula


=SUMPRODUCT(--(LEFT(Apr08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(Apr08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")))+SUMPRODUCT(--(LEFT(Apr08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(Apr08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")))

this counts the number of entries on sheet Apr08WFA when value in range F1 to F4000 is either apr2008 (cellB6) or mar2008 (cellB7).

this works in principle but does not catch all.

I want to amend the formula to catch new entries appearing on a sheet called May08WFA that were not on Apr08WFA but with the same condition of "zz".

So data on May08WFA will be compared against Apr08WFA and new entries counted.

challenge I know but am confident one of your high level users can assist.

 

[DonkeyOte]

assuming that May08WFA range is exactly the same size and position as Apr08WFA the following should work:

=SUMPRODUCT(--(LEFT(Apr08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(Apr08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")))+SUMPRODUCT(--(LEFT(Apr08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(Apr08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")))+SUMPRODUCT(--(LEFT(May08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(May08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B6,"mmmyyyy")),--(May08WFA!$A$1:$A$4000<>Apr08WFA!$A$1:$A$4000))+SUMPRODUCT(--(LEFT(May08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(May08WFA!$F$1:$F$4000,"mmmyyyy")=TEXT(B7,"mmmyyyy")),--(May08WFA!$A$1:$A$4000<>Apr08WFA!$A$1:$A$4000))

However, I'm pretty sure this could be streamlined by one of the formula gurus on here...

Are the dates (1 & 2) always sequential (Mar08, Apr08) or could you have Apr07 and Mar08 for ex ?

 

[DonkeyOte]

I think this works a little better ?

So rather than having SUMPRODUCT1 + SUMPRODUCT2 based on different mmmyyyy criteria the below compresses that into 1 SUMPRODUCT and using a combination of ISERROR check based on mmmyyyy against CONCATENATION of the 2 critera in one go -- then this is repeated for the additional unique items on the 2nd sheet that are not present on the first.

So instead of 4 SUMPRODUCTS being aggregated you have just 2.

=
SUMPRODUCT(--(LEFT(Apr08WFA!$A$1:$A$4000,2)="zz"),--(ISERROR(FIND(TEXT(Apr08WFA!$F$1:$F$4000,"mmmyyyy"),CONCATENATE(TEXT($B$6,"mmmyyyy"),",",TEXT($B$7,"mmmyyyy"))))=FALSE))
+
SUMPRODUCT(--(LEFT(May08WFA!$A$1:$A$4000,2)="zz"),--(ISERROR(FIND(TEXT(May08WFA!$F$1:$F$4000,"mmmyyyy"),CONCATENATE(TEXT($B$6,"mmmyyyy"),",",TEXT($B$7,"mmmyyyy"))))=FALSE),--(May08WFA!$A$1:$A4000<>Apr08WFA!$A$1:$A$4000))

Hope that works for you, let me know.

 

[D-Spark]

Thanks Im I able to use the reply you gave but not refer to B6 and B7.
these are dates whereby I only want the difference between the two sheets as some item entries might be new on the latest sheet "May08" in this instance but F range might show a date way in the past but which has only now been added to the log

 

[DonkeyOte]

Sorry, I don't really understand your note.

If you're saying you want to count instances on sheet1 where month = either date specified in B6 / B7 and text begins "zz" and you want to add to that instances on sheet2 where text begins "zz", month = either date specified in B6/B7 BUT where the text does not exist on sheet1 the latter formula should work.

Please clarify.

Thanks,

 

[D-Spark]

I will try to explain:

I want to compare range B1:B4000 on "May08WFA", for all entries in this range where the first two characters = zz.

These values will then be compared agaisnt agaisnt range B1:B4000 on "Apr08WFA". To identify new zz entries in May08WFA that werent on Apr08WFA

the result will be to count new "zz" items on "May08WFA" that were not present in range B1:B4000 on "Apro08WFA"

 

[DonkeyOte]

regardless of month ?

 

[D-Spark]

Yes regardless of month

 

[D-Spark]

Yes regardless of month

Maybe this problem is bigger than the both of us ! Then again a problem shared is a problem halved

 

[DonkeyOte]

Not really but I'm not on here all day so hadn't had a chance to reply...

If I get your aim correctly I believe the following would work for you, no ?

=
SUMPRODUCT(--(LEFT(APR08WFA!$A$1:$A$4000,2)="zz"),--(ISERROR(FIND(TEXT(APR08WFA!$F$1:$F$4000,"mmmyyyy"),CONCATENATE(TEXT($B$6,"mmmyyyy"),",",TEXT($B$7,"mmmyyyy"))))=FALSE))
+
SUMPRODUCT(--(LEFT(MAY08WFA!$A$1:$A$4000,2)="zz"),--(ISERROR(MATCH(MAY08WFA!$A$1:$A$4000,APR08WFA!$A$1:$A$4000,0)=TRUE)))

works off the assumption that re: the additional MAY08WFA items you're only interested in zz* strings that do not appear on APR08WFA regardless of other criteria (date etc...)