male & female lists
 

Race times are ranked in a worksheet by fastest times regardless of gender.
I wish to create seperate lists of athletes in another worksheet from that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and so
on. All I require is a simple list of females and another list of males from
the main data.

Any ideas?
--
tia

male & female lists
 

On 23 Apr, 11:07, JockW wrote:
Race times are ranked in a worksheet by fastest times regardless of gender.
I wish to create seperate lists of athletes in another worksheet from that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and so
on. All I require is a simple list of females and another list of males from
the main data.

Any ideas?
--
tia

Make your data a stanbdard list or database. This must have:

Headings at the top
Text, numbers, dates below - but only ever one kind per column

Nothing else touching your list

Put the cursor anywhere in the list and fire up ToolsDataPivotTables
(or whatever it is)

Stand back in amazement as all your problems are solved!

male & female lists
 

Assuming that the time is in E, this gives M/F rankings

=$A2&"_"&MATCH($E2,LARGE(IF($A$2:$A$20=$A2,$E$2:$E $20),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$20,$A2)))), 0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JockW" wrote in message
...
Race times are ranked in a worksheet by fastest times regardless of
gender.
I wish to create seperate lists of athletes in another worksheet from that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info
such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and so
on. All I require is a simple list of females and another list of males
from
the main data.

Any ideas?
--
tia

male & female lists
 

You can do it manually of by code using autofilter

manually
Select column A then go to menu Data Filter - Autofilter
then select either Male or Female
copy Column G and Paste where you want the data. You can unselect the
AutoFilter to None if needed.


macro solution

Sheets("Sheet1").Columns("A:A").AutoFilter
Sheets("Sheet1").Cells.AutoFilter field:=1, Criteria1:="Male"
Sheets("Sheet1").Columns("G:G"). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet2").Range("A1")
'turn autofileter off
Sheets("Sheet1").Cells.AutoFilter

"Bob Phillips" wrote:

Assuming that the time is in E, this gives M/F rankings

=$A2&"_"&MATCH($E2,LARGE(IF($A$2:$A$20=$A2,$E$2:$E $20),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$20,$A2)))), 0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JockW" wrote in message
...
Race times are ranked in a worksheet by fastest times regardless of
gender.
I wish to create seperate lists of athletes in another worksheet from that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info
such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and so
on. All I require is a simple list of females and another list of males
from
the main data.

Any ideas?
--
tia

male & female lists
 

Thanks Bob. Sort of worked in that it correctly identified the gender but
gave the reverse order so that the slowest male had the lowest number rather
than the fastest male and so on.
Ideally, I'd just like to 'suck' out the men's info from sheet1 to sheet 6
and the women's to sheet 7 and have them ranked by time with no empty rows
that, for instance VLOOKUP would give. The results are ging on a web site so
user ability to use filters or pivot tables will vary a lot. Which is why I'm
after the simple solution!

ta


"Bob Phillips" wrote:

Assuming that the time is in E, this gives M/F rankings

=$A2&"_"&MATCH($E2,LARGE(IF($A$2:$A$20=$A2,$E$2:$E $20),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$20,$A2)))), 0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JockW" wrote in message
...
Race times are ranked in a worksheet by fastest times regardless of
gender.
I wish to create seperate lists of athletes in another worksheet from that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info
such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and so
on. All I require is a simple list of females and another list of males
from
the main data.

Any ideas?
--
tia

male & female lists
 

Just change LARGE to SMALL in the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JockW" wrote in message
...
Thanks Bob. Sort of worked in that it correctly identified the gender but
gave the reverse order so that the slowest male had the lowest number
rather
than the fastest male and so on.
Ideally, I'd just like to 'suck' out the men's info from sheet1 to sheet 6
and the women's to sheet 7 and have them ranked by time with no empty rows
that, for instance VLOOKUP would give. The results are ging on a web site
so
user ability to use filters or pivot tables will vary a lot. Which is why
I'm
after the simple solution!

ta


"Bob Phillips" wrote:

Assuming that the time is in E, this gives M/F rankings

=$A2&"_"&MATCH($E2,LARGE(IF($A$2:$A$20=$A2,$E$2:$E $20),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$20,$A2)))), 0)

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JockW" wrote in message
...
Race times are ranked in a worksheet by fastest times regardless of
gender.
I wish to create seperate lists of athletes in another worksheet from
that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info
such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and
so
on. All I require is a simple list of females and another list of males
from
the main data.

Any ideas?
--
tia

male & female lists
 

Tried that Bob, thanks.
Got it sorted though by tweaking Joel's response.
Appreciate the effort :)
--



"Bob Phillips" wrote:

Just change LARGE to SMALL in the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JockW" wrote in message
...
Thanks Bob. Sort of worked in that it correctly identified the gender but
gave the reverse order so that the slowest male had the lowest number
rather
than the fastest male and so on.
Ideally, I'd just like to 'suck' out the men's info from sheet1 to sheet 6
and the women's to sheet 7 and have them ranked by time with no empty rows
that, for instance VLOOKUP would give. The results are ging on a web site
so
user ability to use filters or pivot tables will vary a lot. Which is why
I'm
after the simple solution!

ta


"Bob Phillips" wrote:

Assuming that the time is in E, this gives M/F rankings

=$A2&"_"&MATCH($E2,LARGE(IF($A$2:$A$20=$A2,$E$2:$E $20),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$20,$A2)))), 0)

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"JockW" wrote in message
...
Race times are ranked in a worksheet by fastest times regardless of
gender.
I wish to create seperate lists of athletes in another worksheet from
that
information but listed by gender.
Column A has "male" or "female" and the rest of the row has other info
such
as name, time, year and so on up to and including column G.
I am trying to avoid having lists with blank spaces, using filters and
so
on. All I require is a simple list of females and another list of males
from
the main data.

Any ideas?
--
tia