Finding multiple matches of a single input across multiple rows and columns

**gingerdog** · 08-07-2012, 12:58 PM

Hi,
I need to find all of the matches for the text in D2 of the 'find' tab in the data table C2:Z290 of the 'Table1' tab. I have been searching various Excel help forums for a couple days, and believe I need to use an array function with the MATCH and/or INDEX functions. So far I cannot get anything to work. Any help would be much appreciated!
Laura

**NBVC** · 08-07-2012, 01:15 PM

So what result do you want? Do you want the matches to be highlighted in the Table1 tab? Are you only interested in D2? Should the corresponding Sector and Number also match?

**gingerdog** · 08-07-2012, 01:30 PM

Hi,

I would like a list wherever it is easiest to put (tab 2 or a new tab) to show a concatenation of the Sector and Number of the matches from the first tab. I know from doing a manual search of D2 that the first match is in row 2, so I'd like that output to be Beta1. The highlighting that is in there now is remnants of something dumb that I was doing and I should have taken that out.

I'm actually interested in finding the matches for all of the items in column D on tab 2. I figured it would be sort of a manual process involved in copying an array formula down first to find the matches for D2, then starting over with the formula to find the matches for D3. ? So I would be quite happy to just get this working to get matches for D2.

Thanks again!

Laura

**NBVC** · 08-07-2012, 01:51 PM

Ok try this:

add this User-Defined Function (Alt+F11, Insert|Module and paste in VB Editor)

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

Then back in spreadsheet, in E2 enter formula:

=SUBSTITUTE(TRIM(aconcat(TRANSPOSE(IF(ISNUMBER(SEARCH(D2,Table1!$C$2:$Z$290)),Table1!$A$2:$A$290&"-"&Table1!$B$2:$B$290,""))," "))," ",", ")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

Note: This will take a minute or two to calculate....

**gingerdog** · 08-07-2012, 02:54 PM

Wow, thank you so much!! It works perfectly.

Finding multiple matches of a single input across multiple rows and columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Finding multiple matches of a single input across multiple rows and columns

Re: Finding multiple matches of a single input across multiple rows and columns

Re: Finding multiple matches of a single input across multiple rows and columns

Re: Finding multiple matches of a single input across multiple rows and columns

Re: Finding multiple matches of a single input across multiple rows and columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions