Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find match formula
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, -- Thank You! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find match formula
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" wrote in message ... 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, -- Thank You! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find match formula
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" wrote in message ... 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, -- Thank You! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find match formula
This one works. Thanks!
-- Thank You! "Bernard Liengme" wrote: 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" wrote in message ... 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, -- Thank You! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find match formula
This one works too. This one is a definite keeper.
Thank you Mr. Phillips! -- Thank You! "Bob Phillips" wrote: 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" wrote in message ... 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, -- Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find A Match Formula | Excel Worksheet Functions | |||
Using MATCH to find the 2nd match, not only 1st | Excel Discussion (Misc queries) | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
find and match the max | Excel Worksheet Functions |