return all rows where a name is present in 1 of 4 columns

[FreakyGirl]

Hello

I'm working on a spreadsheet where the end goal is to find out how often the same players are playing together.

I want to be able to put a name in a cell & have it return all rows where that name is present.

The problem is that the names may be present in any of 4 different columns.

I found a great formula that is close to my needs but it only works if the name is present in the column specified.

Array formula in A9:

{=INDEX($I$3:$O$44,SMALL(IF(MMULT(($I$3:$O$44=$A$4:$G$4)*1,{1;1;1;1;1;1;1})=COUNTA($A$4:$G$4),MATCH(ROW($I$3:$I$44),ROW($I$3:$I$44)),""),ROWS($H$1:H1)),COLUMNS($H$1:H1))}

For example:
When i put the name 'shar' into D4 (1st column of names) the results show me 1 row because that's the only time 'shar' in that column
record num: 21 'shar' 'oldstray' 'springhiler' 'sparhawk'

When i put 'shar' into F4 (3rd column of names) the results show me 2 rows
record num: 11: 'sparhawk' 'oldstray' 'shar' 'springhiler'
record num: 25: 'sparhawk' 'judyc58' 'shar' 'xtaz2002x'

I need for all records should be shown at one time when 'shar' is entered into D4. note: record num: 38 is missing because 'shar' is also present in column 2

I also want to be able to put additional names in E4, F4 & G4 to narrow the results down even more.

New data is entered daily so the range will need to be flexible. by the end of the month there will be about 700 rows.

Any help would be greatly appreciated.

Thanks in advance for your time.
FreakyGirl

 

[jolivanes]

As per your example

Sub Maybe()
Dim i As Long
    For i = 3 To Cells(Rows.Count, 12).End(xlUp).Row
        If InStr(LCase(Cells(i, 12).Value) & LCase(Cells(i, 13).Value) & LCase(Cells(i, 14).Value) & LCase(Cells(i, 15).Value), _
        LCase(Range("D4").Value)) <> 0 Then MsgBox "Row " & i
    Next i
End Sub

 

[jolivanes]

As per your example (For multiple names)

Sub Maybe()
Dim i As Long, j As Long
    For j = 4 To 7
        For i = 3 To Cells(Rows.Count, 12).End(xlUp).Row
            If InStr(LCase(Cells(i, 12).Value) & LCase(Cells(i, 13).Value) & LCase(Cells(i, 14).Value) & LCase(Cells(i, 15).Value), _
            LCase(Cells(4, j).Value)) <> 0 Then MsgBox "Row " & i
        Next i
    Next j
End Sub

 

[jolivanes]

Or maybe like this?

Sub Or_Like_This()
Dim c As Range, i As Long, j As Long
For Each c In Range("D4:G4")
If Len(c) > 0 Then
        For i = 3 To Cells(Rows.Count, 12).End(xlUp).Row
            For j = 12 To 15
                If LCase(Cells(i, j).Value) = LCase(c) Then Cells(Rows.Count, 17).End(xlUp).Offset(1).Value = c.Value & " in cell " & Cells(i, j).Address(0, 0)
            Next j
        Next i
End If
Next c
End Sub

 

[Peter Bartholomew]

@FreakyGirl
If your data is going to expand, I suggest you use an Excel Table. The key element of a formula is to take each player name in the large table and search the selected names forming the criteria. Summing each row of the table with MMULT allows you to see whether there are matches.
= MMULT( COUNTIFS( selected, players ), {1;1;1;1} ) )
is non-zero if there are any matches.
= MMULT( COUNTIFS( selected, players), {1;1;1;1} ) = COUNTA(selected)
will select the rows where the number of matches is the number of criteria.

From there, our paths diverge. Using Office 365 I simply FILTER the table,
= FILTER( Table1, MMULT( COUNTIFS( selected, players), {1;1;1;1} ) = COUNTA(selected) )
You will most likely to reference the record numbers and use SMALL to consolidate the matches.

 

[FreakyGirl]

Hello

TY both for your quick replies & help.

Jolivanes: I know nothing about vba but I did put the code from your last post into a module & changed the workbook name to .xlsm but I have no idea on how to make it run

Peter: your example is exactly what I'm looking for!! however I only have 2007 so that's not an option for me. is there another way to get the results you show above but without with the FILTER function?

Thanks,
FreakyGirl

 

[jolivanes]

Save the attached file to your computer, open it and try the macros as suggested in the "formula1" sheet.

 

[Peter Bartholomew]

@FreakyGirl I should have kept the file .
I think
= MMULT( COUNTIF( selected, players), {1;1;1;1} ) = COUNTA(selected) )
will work OK so wrapping the formula in IF to give a criterion 'matching Records'
= IF( MMULT( COUNTIF( selected, players), {1;1;1;1} ) = COUNTA(selected) ), record )
will allow you to compact the data with
= INDEX( players, SMALL( matchingRecords, {1;2;3;4;5} ), 0 )
Note: I haven't checked the details of this through, but I think it is broadly correct.

 

[FreakyGirl]

Hello

Jolivanes: The workbook did do the search correctly however I need to have more details shown than just the row number.

Peter: I tried to get the formulas you suggested working but no luck.

After trying some other formula combinations I was able to figure it out by using a helper column.

I added 2 more columns to the data. In R column i combined all 4 names into 1 string & in S column there is a formula to check for any of the 4 names in that string returning a 'yes' if true.

the formula in S4 is:
=IF(AND(ISNUMBER(SEARCH($D$3,R4)),ISNUMBER(SEARCH($E$3,R4)),ISNUMBER(SEARCH($F$3,R4)),ISNUMBER(SEARCH($G$3,R4))),"YES","")

Then I added in another way to search... by the word 'yes'... to bring up any matches where 1 or more names are present in the same row.

It works great even without spelling out the full player name in the search! See attachment.

Even thou I wasn't able to use your solutions thanks to both of you for your time.

FreakyGirl

 

[Peter Bartholomew]

I am glad you are sorted. There is nothing wrong with using a helper column; columns are not exactly in short supply! I am not a great fan of turning a Boolean TRUE into the text string "YES", though. If you leave it as TRUE or 1 it is easier to use in subsequent formulas and it saves the IF clause needed to select the text. If you really want something in plain English, a custom number format could display a 1 as "YES".

As for my formulas, they are almost exclusively array formulas that need to be committed with Ctrl+Shift+Enter on anything but the most up to date versions of Excel.

 

[jolivanes]

Re: however I need to have more details shown than just the row number.
It usually helps if you let us know what is needed.

But it seems your off to the races so that's all that counts.
Good luck