how do i return a list of names when duplication appears

[Guest]

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.

 

[Guest]

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

 

[Peo Sjoblom]

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)