Counting formula: counting new entries between sheets and numbers deleted

[D-Spark]

Hi all,
I have a workbook with three Worksheets

breakdown
May08WFA
Apr08WFA

On "breakdown" I want to acheive a formula in cell O7 to count the number of occurances where a value in range(A1:A2000) on "May08WFA" is different to value in range(A1:A2000) on "Apr08WFA"

What this will show is a number reflecting a count of new entries (account names), between dataset Apr08WFA and May08WFA.

Also if range O8 also could count number of occurnaces where there is a MATCH on account name. This will allow me to see not only the number of new account names but also the number of account names, consistance between the two datasets.

I have experimented with a host of MATCH formula and nothing appears to work

If anyone could provide a formula for both O7 and O8 that would relieve alot of my problems and assist my knowledge of how to acheive this in future.


thanks

darren

 

[thorpyuk]

Re: Counting formula: New entries between sheets and number deleted

Are you talking about an exact match in the range? EG, the range should be in a specified order, with whatever doesn't match being counted?

This formula will count these occurances:

=SUM(IF(Sheet1!A1:A2000<>Sheet2!A1:A2000,1,0))

(array forumla, so confirm with Ctrl, Alt & Enter)

 

[D-Spark]

Thanks have tried this and does return value expected:
think this is due to it needing to MATCH the value from one sheet against the other.

Eg:

if one Sheet "May08WFA" range(A2)="JohnS
then if value range(A6) on Sheet "April08WFA" ="JohnS"

the expected value return would be 1 as there is a MATCH in the value "JohnS" between the two worksheet ranges.

This would be shown as 1 in the formula counting MATCHs


if one Sheet "May08WFA" range(A2)="JohnS
then if vlaue range(A2:A2000) on Sheet "April08WFA does not have anywhere in this range the value "JohnS"

the expected value "counting new additions" would be 1 as there is no MATCH in the value "JohnS" between the two worksheet ranges.

 

[thorpyuk]

Ok, how about a very simple sumproduct:

=SUMPRODUCT(--(Sheet1!A2:A2000=Sheet2!A2:A2000))

This will count up all matching values.... although that said, are they unique?

 

[D-Spark]

I tried this and a figure of 35 was shown:

this due to the first 35 row entries being the same on each sheet

after this a new entry appeared on May08WFA ("A35") which is different to the value in the other data sheet. therefore the count appears to stop.

Manually checked and entry ("A36") on May08WFA and it appears on ("A39") on Apr08WFA

Therefore the count should match this:

Like I said it would appear that the formula is checking the value in the same cell across the two sheet.

However the same value to be (MATCHED) may appear anywhere on range on comparison sheet. (May08WFA in this instance)

So i:
If ("A36") value on May08WFA appears on cell ("A36") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA appears on cell ("A34") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA appears on cell ("A30") the this would be a MATCH and 1 shown as a count

If ("A36") value on May08WFA did not appear anywhere in rangel ("A2:A2000") then this would be a not be a MATCH and 0 shown


Hope this clears things

 

[D-Spark]

Ive cracked it:

=SUMPRODUCT(ISNUMBER(MATCH(Apr08WFA!A2:A3000,May08WFA!A2:A3000,0))+0)

This counts the Matches between the two sheets.
________________
I have tried to expand the matches on the formula above to introduce a condition to the Match where value for comparable ranges is limited to those with the first two left characters as "zz"

SUMPRODUCT(--(LEFT(May08WFA!$A$1:$A$4000,2)="zz"),--(TEXT(May08WFA!$F$1:$F$4000,

does not work !!!

Can anyone help?

 

[D-Spark]

Is this possible ??

 

[D-Spark]

Is this possible ??

Too much time on my hands