getting comparison results in multiple cells

[ayl322]

i'm comparing 2 sheets, the previous day data and the current day data.


if i have in sheet1(previous day):
customerA 1
customerB 2
customerC 1
customerD 4
customerE 3

and in sheet2(current day):
customerA 2
customerB 1
customerC 4
customerD 4
customerG 1

in sheet3, i would like a list of all the customers and the differenc
of the amount between previous day and current day.
if a customer is not included in one of the sheets, it should stil
show up in sheet3.

customerA 1
customerB -1
customerC 3
customerD 0
customerE -3
customerG 1

do i need to write a macro for this? any help would be appreciated.
Thanks

 

[Dave Peterson]

One non-macro approach would be to build a unique list of all the customers.

Insert sheet3
copy the customers from sheet1 into column A.
copy the customers from sheet2 at the bottom of column A
(keep only one header)

Data|filter|advanced filter
Unique records only and copy to B1.
Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR


delete column A (we don't need it anymore)
Then in B2 (header in row 1)
=if(iserror(vlookup(a2,sheet1!$a:$b,2,false)),0,
vlookup(a2,sheet1!$a:$b,2,false))
(one cell)

then in C2, get the info from sheet2:
=if(iserror(vlookup(a2,sheet2!$a:$b,2,false)),0,
vlookup(a2,sheet2!$a:$b,2,false))

and in D2, put =b2-c2

 

[ayl322]

thank you, dave. it works great.
now i have some additional questions...

is there a way to automate this? a new previous day and current da
sheets would be used everyday, and the number of customers could chang
from day to day.
also, what if in sheet3 i wanted to only show the customers whos
amounts are greater than 2?

thanks

 

[Dave Peterson]

I think I'd use Data|Filter|autofilter to show the values I wanted. If I
deleted some data, then someone (the boss!) comes by and says, how about >3? I
wouldn't want to have to do it again.

This worked ok for me:

Option Explicit
Sub testme01()

Dim PrevWks As Worksheet
Dim CurWks As Worksheet
Dim CombWks As Worksheet

Dim PrevTable As Range
Dim CurTable As Range

Dim DestCell As Range
Dim LastRow As Long

Set CurWks = Worksheets("sheet1")
Set PrevWks = Worksheets("sheet2")

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Combined").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set CombWks = Worksheets.Add
CombWks.Name = "Combined"

CurWks.Range("a:a").Copy _
Destination:=CombWks.Range("a1")

Set CurTable = CurWks.Range("a:b")

With CombWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With PrevWks
.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)).Copy _
Destination:=DestCell
Set PrevTable = .Range("a:b")
End With

With CombWks
.Range("a:a").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=CombWks.Range("b1"), Unique:=True
.Columns(1).Delete
.Range("a:a").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("B1").Resize(1, 3).Value _
= Array(PrevWks.Name, CurWks.Name, "Cur - Prev")

With .Range("B2:B" & LastRow)
.Formula = "=vlookup(a2," & PrevTable.Address(external:=True) _
& ",2,false)"
.Value = .Value
End With

With .Range("c2:c" & LastRow)
.Formula = "=vlookup(a2," & CurTable.Address(external:=True) _
& ",2,false)"
.Value = .Value
End With

With .Range("b2:c" & LastRow)
.Replace what:="#n/a", replacement:=0, lookat:=xlWhole
End With

With .Range("d2:d" & LastRow)
.Formula = "=c2-b2"
'don't convert these!
End With

Application.Goto .Range("a2")
ActiveWindow.FreezePanes = True

With .Range("a:d")
.AutoFilter field:=4, Criteria1:=">2"
.Columns.AutoFit
End With

End With

End Sub