Compare lists for additions and subtractions
 

At the start of each month I retrieve a list of 100+ names of people
currently subscribing to a group. I compare this list to the one
retrieved last month. I can easily count the number in the list and the
number increase or decrease. I'd like to calculate the following:

1) New - The number of people not on last month but new to this month's
list.
2) Left List - The number of people on last month's list but not on
this month's list.
3) On Both - The number of people on both lists.

I assume these would be some sort of array formulas but I'm not quite
sure how to get started.

Thanks for the help.

- John

Compare lists for additions and subtractions
 

Join
=SUMPRODUCT(--(COUNTIF(LastMonth,ThisMonth)=0))
Quit
=SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=0))
Continue
=SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=1))

Using Advanced Filter and these formulas can give you
the list of names instead of just the count.

Compare lists for additions and subtractions
 

Very cool. Thanks, Herbert.

I modified your formula a bit so that I could copy it across my sheet
for the various months. Since the size of the array for LastMonth and
ThisMonth are not always the same and I don't have each month of data
named, I used an offset formula to figure out the appropriate range.

For Join I modifed your formula like this:

=SUMPRODUCT(--(COUNTIF(OFFSET(C$8,0,0,C$2,1),OFFSET(D$8,0,0,D$2, 1))=0))

Whe
C$8 is the top of the LastMonth Range
D$8 the top of ThisMonth
C$2 = total number in LastMonth
D$2 = total number in ThisMonth

Thanks for the help.

- John




Herbert Seidenberg wrote:
Join
=SUMPRODUCT(--(COUNTIF(LastMonth,ThisMonth)=0))
Quit
=SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=0))
Continue
=SUMPRODUCT(--(COUNTIF(ThisMonth,LastMonth)=1))

Using Advanced Filter and these formulas can give you
the list of names instead of just the count.