Need help creating reports

  • Thread starter Thread starter Leisa
  • Start date Start date
L

Leisa

I am working on a temporary job for a company that is tracking it's
employee training. Each row in my spreadsheet has the area director's
name, the employee first name, the employee last name, the class code,
class description, start date, end date, and some other colums as well.
One employee may be listed many times (once for each class).

There are no numerical fields to be calculated. What I need to
accomplish is to count the number of individuals (one time only) and
then count the number of classes, etc. I am sure I could use the same
basic formula for achieving any of the fields. I just can't figure out
how to do this.



***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Pivot Table and Advanced Filter. What I would do first though is to ensure you
have a unique employee identifier. Do you have an employee ID, or if not can
you create another column to concatenate the last and first name, eg with your
last names in Col A, first names in Col B, insert a column anywhere (at the end
is fine but I'll assume Col K), and then assuming your headers are in row 1, and
data starts in row 2, in K2 put =A2&", "&B2 and copy down - name the header
'FullName'

Select all the FullName data, do Data / Filter / Advanced Filter, Choose 'Copy
To Another Location', tick 'Unique records only' and then choose where you want
to copy the data to using the range selector from that dialog box. You will now
have a list of unique names, against which you can just do a count, eg assuming
you chose Z1 to output the range to, =COUNTA(Z1:Z50) or if you have used row 1
as the start then take the last row number and knock one off to get the count.

As far as seeing Teachers by class:-

Assuming all your data has headers, select all the data, do Data / Pivot Table
and Chart Report, hit Next, Next, Finish. From the list of fields that will
have just apppeared in front of you, drag FullName over to the far left of the
table, and then from the same list drag it AGAIN but into the middle of the
table. Now drag classcode to the FAR LEFT of the table, and you will now see
each class with all the people in it.
 
Back
Top