IDENTIFY & MARK THE DATA Closed

Question

I have the data in 2 columns i.e A & B. In A column amounts are there(both positive & negative values) and in B column invoice no. are there. Every positive value in A column will have same value with -ve sign. say for ex: 1 is there in A column then -1 will also be there. 
Both will have invoice numbers in B column.  and Now the condition is "If +ve value in Column A has same -ve and both have same invoice no's then in corresponding row in column c should display as "Match".

pls provide me the solution

venkat1926 · Answer

suppose your data is like this 
hdng1	invoice
1	111
2	222
3	4444
4	5555
5	6666
-1	111
-2	3333
-4	4444
-7	77777
try this macro 

Sub test()
Dim r As Range, c As Range, cfind As Range
Dim x
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
'c.Select
x = (-1) * c.Value
'MsgBox x
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then GoTo nextc
If c.Offset(0, 1) = cfind.Offset(0, 1) Then
c.Offset(0, 2) = "match"
End If
nextc:
Next c
End Sub

the result will be 

hdng1	invoice	match?
1	111	match
2	222	
3	4444	
4	5555	
5	6666	
-1	111	match
-2	3333	
-4	4444	
-7	77777	


if you want to retest the macro you have to delete the column C and rerun the macro .  Is this what you want. 
try the macro on your database sheet

venkat1926 · Answer

now my data is like this 

hdng1	invoice
1	111
2	222
3	4444
4	5555
5	6666
-1	111
-2	3333
-4	4444
-7	77777
-1	111

try this modified macro 

Sub test()
Dim r As Range, c As Range, cfind As Range
Dim x, cfind1 As Range, r1 As Range
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
c.Select

x = (-1) * c.Value
'MsgBox x
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then GoTo nextc
If c.Offset(0, 1) = cfind.Offset(0, 1) Then
 Set r1 = Range(Range("A1"), c)
 If WorksheetFunction.CountIf(r1, -x) <= 1 Then
 c.Offset(0, 2) = "match"
 End If
End If
nextc:
Next c
End Sub

IDENTIFY & MARK THE DATA

2 responses

Similar discussions

Subscribe To Our Newsletter!