Returning the Row numbers where one word in contained in a text string, and there are multiple occurances

[ndboston]

First off, this board has been extremely useful to me, but there is something I need to figure out which I could not find through searching the forum so I'm posting my first thread. Here goes:

I am trying to identify the row(s) where a match occurs when there can be multiple occurrences of the match.

	A	B	C
1	john brown	Brown	True
2	Cathy Smith
3	Brown excavating company
4	XYZ Corp
5	Brown Advisors Inc.

<tbody>
</tbody>

The first test I ran in cell C1 with the information above was to determine if "Brown" was located in the range of A1:A5 using formula:
=IF(ISNUMBER(MATCH("*"&B1&"*",A1:A5,0))=TRUE,"True","False")

In the case of the word "Brown" it occurs in the range of A1:A5 three times, so the result of the above formula would be "True"

Now what I would like to do is return the locations, in this case the row number(s), where the word brown is contained range of A1:A5 because in the case of the word Brown, it occurs three separate times in the range of A1:A5. I would like the resulting value of the formula in this case to be "1, 3, 5" indicating the word Brown occurs in rows 1, 3, and 5. The formula also needs to work in case there is only one match as well.

I tried using the below array formula:
={MATCH(FALSE,ISERROR(SEARCH(B1,'Working List of Vendors'!$A$1:$A$5)),0)} , but it would only return the first occurrence of the match which in this case would be row 1, or "1"

Obviously, this is just a small example of the data set I am using but if I can get the formula figured out for this small sample I can apply it to what I am working with.

Many Thanks

 

[XOR LX]

Hi,

Do you actually want the returned array as a comma-delimited text string in a single cell? Or just to generate the array to be e.g used in further formula(s)?

Regards

 

[ndboston]

Hi,

Do you actually want the returned array as a comma-delimited text string in a single cell? Or just to generate the array to be e.g used in further formula(s)?

Regards

The range I'm searching is much larger than the one I listed in the example where there is only 5 rows. The range spans more than 1,000 rows of names that might contain the word I'm searching here which is "Brown". I'm going to be giving this to someone to research the relationship of any match so I want them to be able to know what row(s) the match occurs in so they can go to those rows and research the relationship between "Brown" and all the matches where "Brown" is contained in the name somewhere.

I really don't care how I get the answer and the truth is I don't have a deep background in the use of arrays so I'm not sure I can answer your question directly. I've mostly been searching the web and finding formulas that work for the purpose that I need and that's why I used an array in my example.

 

[XOR LX]

If you're happy with having the row numbers returned in separate cells in a single column, this array formula and copied down (until you start to get blanks for the results) will do that:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH("*"&$B$1&"*",$A$1:$A$5)),ROW($A$1:$A$5)),ROWS($1:1)),"")

Regards

 

[ndboston]

If you're happy with having the row numbers returned in separate cells in a single column, this array formula and copied down (until you start to get blanks for the results) will do that:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH("*"&$B$1&"*",$A$1:$A$5)),ROW($A$1:$A$5)),ROWS($1:1)),"")

Regards

That is very useful, thank you. Is there a way to have it return the row numbers all in one cell e.g. cell C1 would result in "1, 3, 5", meaning the occurrence happened in rows 1, 3 and 5?

If not I will use your formula, but instead of copying down in the column I will copy across in the same row.

 

[XOR LX]

If you want to copy across instead, change to:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH("*"&$B$1&"*",$A$1:$A$5)),ROW($A$1:$A$5)),COLUMNS($A:A)),"")

As regards concatenating into a single cell, you'd need a VBA-based solution for that (in as much as any formula-based solution would quickly become unfeasible beyond a small number of returns).

I can't help you with that, I'm afraid, though if you choose to go down that line (and assuming no-one with expertise in that area picks up on this thread), perhaps you can re-'bump' this in a day or two.

Regards

 

[Aladin Akyurek]

D1, control+shift+enter, not just enter:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&B$1&" "," "&$A$1:$A$5&" ")),
  ", "&ROW($A$1:$A$5),"")),1,2,"")

In order to run this formula, you need to add the following code as a module to your workbook, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[ndboston]

Thank you so much for taking the time to do this. I entered the formula as you said and then also added the module and cell D1 equaled "1,3,5" just like I wanted it to. Now I copied that same formula down to the next cell where the only match was cell A2 so the result should be "2" however the result is still "1,3,5". Cell B2 is "Smith" in my workbook so A2 is the only match for Smith.

My list in column A is roughly 1,600 records and my list in column B is roughly 800 so I will need to copy this equation down and have it work appropriatly. Will this be complicated to do or am I just missing something obvious. Thanks in advance for all your help!

D1, control+shift+enter, not just enter:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(" "&B$1&" "," "&$A$1:$A$5&" ")),
  ", "&ROW($A$1:$A$5),"")),1,2,"")

In order to run this formula, you need to add the following code as a module to your workbook, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[Aladin Akyurek]

Thank you so much for taking the time to do this. I entered the formula as you said and then also added the module and cell D1 equaled "1,3,5" just like I wanted it to. Now I copied that same formula down to the next cell where the only match was cell A2 so the result should be "2" however the result is still "1,3,5". Cell B2 is "Smith" in my workbook so A2 is the only match for Smith.

My list in column A is roughly 1,600 records and my list in column B is roughly 800 so I will need to copy this equation down and have it work appropriatly. Will this be complicated to do or am I just missing something obvious. Thanks in advance for all your help!

Change B$1 to $B1.