find match formula

[maijiuli]

Hello,

I am looking for a formula that will find matching records and also do a
caluculation.

I have 4 columns set up. A, B, C and D.

Column A is Social Security Number
Column B is Election Amount #1
Column C is Social Security Number
Column D is Election Amount #2

An example:

Col A Col B Col C Col D
123456789 $100,000 123456789 $50,000
111111111 $250,000 222222222 $200,000
222222222 $300,000


Column A & B have 69 rows and should remain with each other. Meaning if one
row moves the other row should also. Column C & D have 33 rows.

I want to find and somehow line up all the matching records Col A = Col C so
I can easily look and figure out which elections will be allowed and which
ones will not. The rule is the second election amount cannot be more than
50% of the first election amount of the same member.

If there is an easier way please let me know.

Thank you very much,

 

[Bernard Liengme]

Have a look at VLOOKUP in Help then come back for more.
=VLOOKUP(A1,$C$1:$D$D49,2,FALSE) will return the $50,000 value
=VLOOKUP(A2,$C$1:$D$D49,2,FALSE) will return #N/A since there is no match
=VLOOKUP(A3 ,$C$1:$D$D49,2,FALSE) will return the $200,000 value
best wishes

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iRow = 0
On Error Resume Next
iRow = Application.Match(.Cells(i, "A").Value, .Columns(3), 0)
On Error GoTo 0
If iRow > 0 Then
.Cells(iRow, "C").Resize(, 2).Copy .Cells(i, "C")
.Cells(iRow, "C").Resize(, 2).ClearContents
If .Cells(i, "D").Value > .Cells(i, "B").Value / 2 Then
.Cells(i, "A").Resize(, 4).Interior.ColorIndex = 6
End If
End If
Next i
If .Cells(1, "C").Value = .Cells(1, "A").Value Then
If .Cells(1, "D").Value > .Cells(1, "B").Value / 2 Then
.Cells(1, "A").Resize(, 4).Interior.ColorIndex = 6
End If
End If

End With

End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[maijiuli]

This one works. Thanks!
--
Thank You!

Have a look at VLOOKUP in Help then come back for more.
=VLOOKUP(A1,$C$1:$D$D49,2,FALSE) will return the $50,000 value
=VLOOKUP(A2,$C$1:$D$D49,2,FALSE) will return #N/A since there is no match
=VLOOKUP(A3 ,$C$1:$D$D49,2,FALSE) will return the $200,000 value
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

 

[maijiuli]

This one works too. This one is a definite keeper.

Thank you Mr. Phillips!