i have a worksheet that contains lists of proposed attendees for various
courses with an identifier if they actually attended. the names in the list
can be repeated if they proposed to attend more than one course. i would like
to create another worksheet that will return the name of each person proposed
to attend with the number of courses actually attended. the main problems i
am having is how to return the name once even if it is repeated throughout
the list and how to count the number of courses each person has attended. i
am not sure if it is possible in Excel or if i should be using Access instead.

Assuming your data in worksheet 1
Data Filter Advanced Filter select Copy to another location List
range: select your range copy to: a blank cell on the same worksheet
click Unique records only OK

then copy the filter range to A1 Worksheet 2
B2 =COUNTIF(Sheet1!$A$2:$A$100,Sheet2!$A2)
drag from b2 all the way down as far as needed


Formula

"Shaun" wrote:

i have a worksheet that contains lists of proposed attendees for various
courses with an identifier if they actually attended. the names in the list
can be repeated if they proposed to attend more than one course. i would like
to create another worksheet that will return the name of each person proposed
to attend with the number of courses actually attended. the main problems i
am having is how to return the name once even if it is repeated throughout
the list and how to count the number of courses each person has attended. i
am not sure if it is possible in Excel or if i should be using Access instead.

Or if you start from the other sheet you can copy the filtered data to that
other sheet in one fell swoop
just select a cell in the worksheet 2, then start to apply the filter.

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

"Teethless mama" wrote in message
...
Assuming your data in worksheet 1
Data Filter Advanced Filter select Copy to another location List
range: select your range copy to: a blank cell on the same worksheet
click Unique records only OK

then copy the filter range to A1 Worksheet 2
B2 =COUNTIF(Sheet1!$A$2:$A$100,Sheet2!$A2)
drag from b2 all the way down as far as needed


Formula

"Shaun" wrote:

i have a worksheet that contains lists of proposed attendees for various
courses with an identifier if they actually attended. the names in the
list
can be repeated if they proposed to attend more than one course. i would
like
to create another worksheet that will return the name of each person
proposed
to attend with the number of courses actually attended. the main problems
i
am having is how to return the name once even if it is repeated
throughout
the list and how to count the number of courses each person has attended.
i
am not sure if it is possible in Excel or if i should be using Access
instead.