array formula to show differences in 2 lists?
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 |
array formula to show differences in 2 lists?
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 |
All times are GMT +1. The time now is 10:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com