IDENTIFY & MARK THE DATA
Closed
kishan
-
10 May 2010 à 02:54
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 11 May 2010 à 01:07
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 11 May 2010 à 01:07
Related:
- IDENTIFY & MARK THE DATA
- Check mark in word - Guide
- How to copy data from one excel sheet to another - Guide
- Tmobile data check - Guide
- The system could not identify your location viber - Guide
- Mint mobile data not working ✓ - Network Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
10 May 2010 à 06:09
10 May 2010 à 06:09
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
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
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
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
11 May 2010 à 01:07
11 May 2010 à 01:07
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
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
10 May 2010 à 23:04