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

Report •

✔ Best Answer
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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question