| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find match from 1st 3 columns and return the row number
I need help writing a function that would find a match in the first 3 columns
in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
Maybe...
=match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Will return 1 if the first row of A4:C99 matches (row 4). Will return 2 if the second row of A4:C99 matches (row 5). So if you want to see 4 when it matches row 4, add 3 to the formula: =3+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) or =row(a4:a99)-1+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) (If you want to adjust the ranges) HelpMe wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
Try this function
Function FindRowAfterMatch() As Long With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
Thank you! This is very helpful but I have a couple of questions:
1- where is the row number of the match? 2- FindRowAfterMatch is the row number of the next non blank in col A? 3- What happens if combination found is the last row in rowcount, what would be the value of FindRowAfterMatch? Thank you very much for your help. "Joel" wrote: Try this function Function FindRowAfterMatch() As Long With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
Thanks!
"Dave Peterson" wrote: Maybe... =match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Will return 1 if the first row of A4:C99 matches (row 4). Will return 2 if the second row of A4:C99 matches (row 5). So if you want to see 4 when it matches row 4, add 3 to the formula: =3+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) or =row(a4:a99)-1+match(1,(a4:a99=a2)*(b4:b99=b2)*(c4:c99=c2),0) (If you want to adjust the ranges) HelpMe wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
The row number gets put into the return parameter of the function
FindRowAfterMatch = RowCount Nothing will get returned if a match isn't found or the match is in the last row. In that case FindRowAfterMatch will be null. If you want something to be returned then the modification below wil return -1 if nothing is found Function FindRowAfterMatch() As Long FindRowAfterMatch = -1 With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: Thank you! This is very helpful but I have a couple of questions: 1- where is the row number of the match? 2- FindRowAfterMatch is the row number of the next non blank in col A? 3- What happens if combination found is the last row in rowcount, what would be the value of FindRowAfterMatch? Thank you very much for your help. "Joel" wrote: Try this function Function FindRowAfterMatch() As Long With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find match from 1st 3 columns and return the row number
Thanks! I modified it a bit to also get the Match Row Number . You are awesome.
"Joel" wrote: The row number gets put into the return parameter of the function FindRowAfterMatch = RowCount Nothing will get returned if a match isn't found or the match is in the last row. In that case FindRowAfterMatch will be null. If you want something to be returned then the modification below wil return -1 if nothing is found Function FindRowAfterMatch() As Long FindRowAfterMatch = -1 With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: Thank you! This is very helpful but I have a couple of questions: 1- where is the row number of the match? 2- FindRowAfterMatch is the row number of the next non blank in col A? 3- What happens if combination found is the last row in rowcount, what would be the value of FindRowAfterMatch? Thank you very much for your help. "Joel" wrote: Try this function Function FindRowAfterMatch() As Long With ActiveSheet LastRow = .Range("A" & Rows.Count).End(xlUp).Row FoundMatch = False For RowCount = 2 To LastRow If FoundMatch = False Then If .Range("A" & RowCount) = "DDD" And _ .Range("B" & RowCount) = "EEE" And _ .Range("C" & RowCount) = "FFF" Then FoundMatch = True End If Else If .Range("A" & RowCount) < "" Then FindRowAfterMatch = RowCount Exit For End If End If Next RowCount End With End Function "HelpMe" wrote: I need help writing a function that would find a match in the first 3 columns in a sheet. Then return the row number where the combination was found. Then return the row number of the next non blank row it finds. Here is an example: Row1 ColA ColB ColC Row2 DDD EEE FFF Row3 Row4 HHH I would need to search for the row that contains DDD in ColA, if match then look for EEE in ColB, and if match then look for FFF in ColC. Once found then return the row number where found, in this case 2. Then find the next value in ColA that is non blank and return this row number, in this case 4. I really appreciate your help with this. God bless you all for all the help you provide! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return proper match from three columns | Excel Worksheet Functions | |||
| Match 2 Columns, Return 3rd, Differing Match Types | Excel Worksheet Functions | |||
| Match Columns and return value | Excel Programming | |||
| match 2 columns and return value of another | Excel Worksheet Functions | |||
| Find closest match and return next highest number in range | Excel Discussion (Misc queries) | |||
Linear Mode
