PDA

View Full Version : Validate Date and Compare Amounts



Pratap 1987
08-12-2016, 05:33 AM
Hi there,

Could one of you please help me in building a macro for the below requirement.
Basically, I am trying to compare the Dates and Amounts in 2 different columns and highlight exceptions:

I have a spreadsheet - Where Col K & V have Dates on it - And Col N & Z has amounts on it.
Need a macro to look for the Dates from Col K to V and if match found - Compare the amounts from Col N to Z.
Any mismatch in amounts greater than 0.99 or any amounts which does not have a match should be highlighted in Red.

Points to note:
Amounts in Col N & Z - Could be in + or - sign - So the amounts are compared - It should look for opposite signs.
Mismatch should be highlighted in both ways - ie, Mismatches in Col N and Col Z has to be highlighted (not just 1 Col).





K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z


1
Date


Db (They)







Date



Cr (We)


2
1-Jul-16


-100.00







2-Jul-16






3
1-Jul-16










2-Jul-16






4
2-Jul-16










5-Jul-16



700.00


5
2-Jul-16










6-Jul-16






6
4-Jul-16


-350.00







4-Jul-16






7
4-Jul-16










4-Jul-16



350.00


8
5-Jul-16


-700.00







1-Jul-16



100.00


9
6-Jul-16


-111.00







1-Jul-16

mikerickson
08-13-2016, 11:49 AM
I note that there are duplicate dates in each of those columns.

How do you want that handled? Are we looking at the sum of the values for a particular date or at each of the entries for that date?

e.g. what if Z9 contained 30 (as well as Z8 containing 100)

SamT
08-13-2016, 03:47 PM
2 @ 2d arrays
First dimension = Row number
Second dimension debit array = Dateserial + (dollar value /10^5) * -1
Second Dimension credit array = Dateserial + Dollar value/10^5

Sort both arrays by second dimension

For i to Ubound
If not cr(i,2) = db(i,2) then error, check if +- 99/10^7
If not then mark Cells both arrays(i,1)

Pratap 1987
08-13-2016, 11:58 PM
Hi Sam, I'm not too familiar with VBAs. Would you be able to build one for me? Please

Pratap 1987
08-14-2016, 12:00 AM
Hi Mike - They should be handled at each of the entries for that date.

Paul_Hossler
08-14-2016, 07:13 AM
Brute force, and not extremely efficient, but I tried to make it easily maintainable



Option Explicit

Sub Compare()
Dim rDate1 As Range, rAmt1 As Range, rDate2 As Range, rAmt2 As Range
Dim iLastRow1 As Long, i As Long, iLastRow2 As Long, j As Long

'init
Set rDate1 = ActiveSheet.Range("K:K")
Set rAmt1 = ActiveSheet.Range("N:N")
Set rDate2 = ActiveSheet.Range("V:V")
Set rAmt2 = ActiveSheet.Range("Z:Z")
'clear color
rDate1.Interior.ColorIndex = xlNone
rAmt1.Interior.ColorIndex = xlNone
rDate2.Interior.ColorIndex = xlNone
rAmt2.Interior.ColorIndex = xlNone

'get last rows
iLastRow1 = rDate1.Cells(1, 1).End(xlDown).Row
iLastRow2 = rDate2.Cells(1, 1).End(xlDown).Row
'compare data and abs amount
For i = 2 To iLastRow1
For j = 2 To iLastRow2
If (rDate1(i).Value = rDate2(j).Value) And (Abs(rAmt1(i).Value) = Abs(rAmt2(j).Value)) Then
rDate1(i).Interior.Color = vbGreen
rAmt1(i).Interior.Color = vbGreen
rDate2(j).Interior.Color = vbGreen
rAmt2(j).Interior.Color = vbGreen
End If
Next j
Next i
'fill 1 not matched
For i = 2 To iLastRow1
If rDate1(i).Interior.ColorIndex = xlNone Then
rDate1(i).Interior.Color = vbRed
rAmt1(i).Interior.Color = vbRed
End If
Next i
'fill 2 not matched
For j = 2 To iLastRow2
If rDate2(j).Interior.ColorIndex = xlNone Then
rDate2(j).Interior.Color = vbRed
rAmt2(j).Interior.Color = vbRed
End If
Next j
End Sub

mikerickson
08-14-2016, 03:23 PM
Hi Mike - They should be handled at each of the entries for that date.

What does this mean?
Could you attach a sample workbook (with a few duplicate entries) and an example of the desired result?