FreakyGirl
Member
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:
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
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:
Code:
{=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
Attachments
Last edited: