Find two different, unknown, names in list

  • Thread starter Thread starter Ed_M
  • Start date Start date
E

Ed_M

I have a football spreadsheet with different picks listed in columns.
For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
How can I have both names displayed at the bottom of the column: Ala
and Tex? The first name is easy, simply select the top pick in the
column. The second pick is the "other" name. How can this be done?
Thanks,
Ed M.
 
I have a football spreadsheet with different picks listed in columns.
For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
How can I have both names displayed at the bottom of the column: Ala
and Tex?  The first name is easy, simply select the top pick in the
column.  The second pick is the "other" name.  How can this be done?
Thanks,
Ed M.
Click to expand...

If there are only two and you want them at the bottom of the column in
separate cells then, say the range holding the picks is A2:A14...
=A2 in A15 for the first (as you stated)
=INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
1)) in A16 for the other. This is an array formula so must be entered
using the Ctrl+Shift+Enter key combination.

If there are only two and you want them together in the same cell
separated by a comma and a space then
=A2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
+1,""),1)) in A15. This is an array formula so must be entered using
the Ctrl+Shift+Enter key combination.

If there are more than two and you want them at the bottom of the
column in separate cells then...
=IF(ROW($A1)>SUMPRODUCT(($A$2:$A$14<>"")/(COUNTIF($A$2:$A$14,$A$2:$A
$14)+($A$2:$A$14=""))),"",INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A$14,$A
$2:$A$14)<>ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1,"",ROW($A$2:$A$14)-
MIN(ROW($A$2:$A$14))+1),ROW($A1)))) in A15 filled down to accommodate
the likely maximum number of different picks. This is an array formula
so must be entered using the Ctrl+Shift+Enter key combination.

Ken Johnson
 
If there are only two and you want them at the bottom of the column in
separate cells then, say the range holding the picks is A2:A14...
=A2 in A15 for the first (as you stated)
=INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
1)) in A16 for the other. This is an array formula so must be entered
using the Ctrl+Shift+Enter key combination.

If there are only two and you want them together in the same cell
separated by a comma and a space then
=A2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
+1,""),1)) in A15. This is an array formula so must be entered using
the Ctrl+Shift+Enter key combination.

If there are more than two and you want them at the bottom of the
column in separate cells then...
=IF(ROW($A1)>SUMPRODUCT(($A$2:$A$14<>"")/(COUNTIF($A$2:$A$14,$A$2:$A
$14)+($A$2:$A$14=""))),"",INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A$14,$A
$2:$A$14)<>ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1,"",ROW($A$2:$A$14)-
MIN(ROW($A$2:$A$14))+1),ROW($A1)))) in A15 filled down to accommodate
the likely maximum number of different picks. This is an array formula
so must be entered using the Ctrl+Shift+Enter key combination.

Ken Johnson
Click to expand...

Oops!
The MATCH function in the last one should have its 3rd argument set to
zero...
=IF(ROW($A1)>SUMPRODUCT(($A$2:$A$14<>"")/(COUNTIF($A$2:$A$14,$A$2:$A
$14)+($A$2:$A$14=""))),"",INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A
$14,$A
$2:$A$14,0)<>ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1,"",ROW($A$2:$A
$14)-
MIN(ROW($A$2:$A$14))+1),ROW($A1))))

Ken Johnson
 
If there are only two and you want them at the bottom of the column in
separate cells then, say the range holding the picks is A2:A14...
=A2 in A15 for the first (as you stated)
=INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
1)) in A16 for the other. This is an array formula so must be entered
using the Ctrl+Shift+Enter key combination.

If there are only two and you want them together in the same cell
separated by a comma and a space then
=A2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
+1,""),1)) in A15. This is an array formula so must be entered using
the Ctrl+Shift+Enter key combination.

If there are more than two and you want them at the bottom of the
column in separate cells then...
=IF(ROW($A1)>SUMPRODUCT(($A$2:$A$14<>"")/(COUNTIF($A$2:$A$14,$A$2:$A
$14)+($A$2:$A$14=""))),"",INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A$14,$A
$2:$A$14)<>ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1,"",ROW($A$2:$A$14)-
MIN(ROW($A$2:$A$14))+1),ROW($A1)))) in A15 filled down to accommodate
the likely maximum number of different picks. This is an array formula
so must be entered using the Ctrl+Shift+Enter key combination.

Ken Johnson
Click to expand...

Ken,
Impressive and thorough!
I looked at it for a while. Could you please explain: IF(C3:C50<>C3
Also, what does the last ,1)) do?
What makes this an array?
Any other comments?
Thanks,
Ed M
 
Ken,
Impressive and thorough!
I looked at it for a while.  Could you please explain: IF(C3:C50<>C3
Also, what does the last ,1)) do?
What makes this an array?
Any other comments?
Thanks,
Ed M
Click to expand...

Hi Ed_M,

If you select 48 vertical cells then type into the active cell...
=ROW(C3:C50)-MIN(ROW(C3:C50))+1
then press Ctrl+Shift+Enter you will see an array of number from 1 up
to 48.
If you select a different bunch of 48 vertical cells then type into
the active cell
=IF(C3:C50<>C3,ROW(C3:C50)-MIN(ROW(C3:C50))+1,"")
then press Ctrl+Shift+Enter you will see an array of numbers and
blanks.
The first element in the array has to be a blank because C3=C3.
The second element will also be blank if C4=C3. If C4<>C3 then the
second element will equal 2.
Similarly, the third element will be blank if C5=C3, otherwise it will
equal 3, etc.
So, to get to the "other" Pick in that column its just a matter of
selecting the smallest number in the array of blanks and numbers
(although it could be any of the numbers) then using the INDEX
function with that number to locate the "other" pick.

Ken Johnson
 
Hi Ed_M,

If you select 48 vertical cells then type into the active cell...
=ROW(C3:C50)-MIN(ROW(C3:C50))+1
then press Ctrl+Shift+Enter you will see an array of number from 1 up
to 48.
If you select a different bunch of 48 vertical cells then type into
the active cell
=IF(C3:C50<>C3,ROW(C3:C50)-MIN(ROW(C3:C50))+1,"")
then press Ctrl+Shift+Enter you will see an array of numbers and
blanks.
The first element in the array has to be a blank because C3=C3.
The second element will also be blank if C4=C3. If C4<>C3 then the
second element will equal 2.
Similarly, the third element will be blank if C5=C3, otherwise it will
equal 3, etc.
So, to get to the "other" Pick in that column its just a matter of
selecting the smallest number in the array of blanks and numbers
(although it could be any of the numbers) then using the INDEX
function with that number to locate the "other" pick.

Ken Johnson
Click to expand...

The ",1))" part makes the SMALL function return the smallest number in
the array.
(though any of the numbers in the array would work provided there are
only 2 different Picks in the column.
It's an array function because it uses an array of blanks and numbers
resulting from the comparison of all the column values with the top
value in the column.

Ken Johnson
 
Back
Top