# Solved How to establish Match function to get more than one result

October 4, 2014 at 08:16:23
Specs: Windows 7
 In row AF2:AF151 I have a list of numbers between 0 and 13 and in row A1:A151 a list of names. In cell G154 which is merged with several other cells, I want to record all the names who have matching number 13.

See More: How to establish Match function to get more than one result

October 8, 2014 at 11:12:21
 ```Sub Loop13Names() 'Loop through range, cell by cell For Each num In Range("AF2:AF151") 'Build temp string if 13 found If num = 13 Then tempnames = tempnames & Range("A" & num.Row) & "; " End If Next 'Strip off extra "; " and place result in G154 'or place No Winners in G154 If tempnames <> "" Then Range("G154") = Left(tempnames, Len(tempnames) - 2) Else: Range("G154") = "No Winners" End If End Sub```or```Sub Find13Names() 'Use Find and FindNext to search for 13 within search range With Range("AF1:AF151") Set num = .Find(13, lookat:=xlWhole, LookIn:=xlValues) If Not num Is Nothing Then firstAddress = num.Address Do 'Build temp string for each 13 found tempnames = tempnames & Range("A" & num.Row) & "; " Set num = .FindNext(num) Loop While Not num Is Nothing And num.Address <> firstAddress End If End With 'Strip off extra "; " and place result in G154 'or place No Winners in G154 If tempnames <> "" Then Range("G154") = Left(tempnames, Len(tempnames) - 2) Else: Range("G154") = "No Winners" End If End Sub```

#1
October 4, 2014 at 14:22:44
 Let's start with making sure we use the correct terminology to ensure we avoid confusion.A1:A151 and AF2:AF151 are called "ranges" not rows. A group of cells is generally referred to as a range. A and AF are Columns, 1:151 are Rows, A1:A151 is a range.OK, with that out of the way, allow me to ask a few of questions...You state that your numbers start in Row 2 but your names start in Row 1. Is there are a reason that both sets of values don't start in the same Row? The reason I ask is that the name in A1 would never be returned since there would never be a 13 in AF1 since it is outside of the range that contains the numbers.Why do you want all of the names to end up in a single cell? What exactly are you trying to accomplish?To get all of the names in a single cell could easily be done with a macro (VBA), but I'm not sure you'll be able accomplish that with a formula. Even if you could, how would you want the names separated? A punctuation mark, such as a comma? A line feed? Something else? Again, I can't think of a way to accomplish that with a formula, but until I know what you trying to accomplish by returning all the names in one cell, I can't offer any real suggestions.

Report •

#2
October 4, 2014 at 15:08:29
 Sorry, the cell ranges should both be 2:151.It is for a tipping competition and I want the result of the winners names to be listed as such with the names separated by a ;

Report •

#3
October 4, 2014 at 19:38:34
 I hope you'll accept a macro 'cuz I don't think it can be done with a formula.Of course, I still don't understand why the results have to be in one cell. message edited by DerbyDad03

Report •

Related Solutions

#4
October 5, 2014 at 03:42:43
 Yea no problem with a macro

Report •

#5
October 5, 2014 at 16:07:28
 Either one of these macros should give you the result you are looking for. The first searches AF2:AF151, cell by cell, looking for the value 13. Each time a 13 is found, the code extends the tempString with the value in the corresponding Row from Column A and adds a semi-colon and a space. Once all of the 13's have been found the code strips off the extra semi-colon and space and places the final string in G154.The second piece of code does essentially the same thing except that instead of looking at each and every cell in the range, it uses the Excel Find feature to search for each 13.In theory, the second piece of code is more efficient, but for such a relatively small range of cells, you probably won't notice any difference in run time.OK...now that I've provided a couple of solutions, could you please return the favor and explain why you want all of the names in one cell?This code loops through AF2:AF151, checking each cell for a 13:```Sub Loop13Names() 'Loop through range, cell by cell For Each num In Range("AF2:AF151") 'Build temp string if 13 found If num = 13 Then tempNames = tempNames & Range("A" & num.Row) & "; " End If Next 'Strip off extra "; " and place result in C1 Range("G154") = Left(tempNames, Len(tempNames) - 2) End Sub ```This code use the Find method instead of checking each individual cell:```Sub Find13Names() 'Use Find and FindNext to search for 13 within search range With Range("AF1:AF151") Set num = .Find(13, lookat:=xlWhole, LookIn:=xlValues) If Not num Is Nothing Then firstAddress = num.Address Do 'Build temp string for each 13 found tempNames = tempNames & Range("A" & num.Row) & "; " Set num = .FindNext(num) Loop While Not num Is Nothing And num.Address <> firstAddress End If End With 'Strip off extra "; " and place result in C1 Range("G155") = Left(tempNames, Len(tempNames) - 2) End Sub```

Report •

#6
October 8, 2014 at 03:58:52
 Because for everything to fit on the one page i need to record the names on a single line and i cannot alter the size of the cells without it affecting the set up for all the other information on the spreadsheet page.The macro's function fine except that if there is no value to return an error message appears "Run time error '5'; invalid procedure call or argument".Is it possible for a value "No winners" to be recorded to prevent the error message appearing.

Report •

#7
October 8, 2014 at 11:12:21
 ```Sub Loop13Names() 'Loop through range, cell by cell For Each num In Range("AF2:AF151") 'Build temp string if 13 found If num = 13 Then tempnames = tempnames & Range("A" & num.Row) & "; " End If Next 'Strip off extra "; " and place result in G154 'or place No Winners in G154 If tempnames <> "" Then Range("G154") = Left(tempnames, Len(tempnames) - 2) Else: Range("G154") = "No Winners" End If End Sub```or```Sub Find13Names() 'Use Find and FindNext to search for 13 within search range With Range("AF1:AF151") Set num = .Find(13, lookat:=xlWhole, LookIn:=xlValues) If Not num Is Nothing Then firstAddress = num.Address Do 'Build temp string for each 13 found tempnames = tempnames & Range("A" & num.Row) & "; " Set num = .FindNext(num) Loop While Not num Is Nothing And num.Address <> firstAddress End If End With 'Strip off extra "; " and place result in G154 'or place No Winners in G154 If tempnames <> "" Then Range("G154") = Left(tempnames, Len(tempnames) - 2) Else: Range("G154") = "No Winners" End If End Sub```