I'm needing to compare two lists of stores each week, and am wondering if
this can be done using some kind of array formula, or if vba would be needed.
From one week to the next, the list of stores will not change much, but
there could be a few stores that drop off, and a few new stores added. I
eventually want to graph for a given week, the count of stores, and the
number of new stores added, as well as the number of stores that fell off the
list. The data could look something like this:

week1 week2
1 1
2 2
3 4
4 5
5 6
7

In this example, my store count in week2 would be 6, with 1 store falling
off, and 2 new stores being added. Is there a formula that would tell me the
number of stores that fell off? And another formula to tell me the number of
stores that were added?

Jeff

Here are a pair of UDFs. The assumption is that in your columns, the top
cell is a header cell and that the real data starts on row #2:

Function falling_off(r1 As Range, r2 As Range) As Integer
falling_off = 0
c1 = r1(1).Column
c2 = r2(1).Column
nr1 = Cells(Rows.Count, c1).End(xlUp).Row
nr2 = Cells(Rows.Count, c2).End(xlUp).Row

For i = 2 To nr1
v = Cells(i, c1).Value
If Application.WorksheetFunction.CountIf(r2, v) = 0 Then
falling_off = falling_off + 1
End If
Next
End Function





Function added_on(r1 As Range, r2 As Range) As Integer
added_on = 0
c1 = r1(1).Column
c2 = r2(1).Column
nr1 = Cells(Rows.Count, c1).End(xlUp).Row
nr2 = Cells(Rows.Count, c2).End(xlUp).Row

For i = 2 To nr2
v = Cells(i, c2).Value
If Application.WorksheetFunction.CountIf(r1, v) = 0 Then
added_on = added_on + 1
End If
Next
End Function




Lets say your data is in columns A & B. In some other cell, enter:
=falling_off(A:A,B:B)
to display 1

and in another cell enter:
=added_on(A:A,B:B)
to display 2
--
Gary''s Student - gsnu200771 gsnuxx


"goofy11" wrote:

I'm needing to compare two lists of stores each week, and am wondering if
this can be done using some kind of array formula, or if vba would be needed.
From one week to the next, the list of stores will not change much, but
there could be a few stores that drop off, and a few new stores added. I
eventually want to graph for a given week, the count of stores, and the
number of new stores added, as well as the number of stores that fell off the
list. The data could look something like this:

week1 week2
1 1
2 2
3 4
4 5
5 6
7

In this example, my store count in week2 would be 6, with 1 store falling
off, and 2 new stores being added. Is there a formula that would tell me the
number of stores that fell off? And another formula to tell me the number of
stores that were added?

Jeff