rank employees based on performances

hello,

on sheet 1, i have a list of employee performances
column A = Date;
column B = Employee ID;
column C = Number of successful sales on that specific date

Employees are listed a number of times in column B because they have worked on many different dates.


I would like sheet 2 to have a top 5 salesman list.
in column A, i would like Employee ID with the TOTAL number of sales next to it (of course ranked from highest to lowest)

remember each employee is mentioned multiple times on sheet 1 so you would have to add all the sales they did on every date they worked.

thank you if you can help

Use a pivot table.

See attachment for an example.

Why don't you want to use a pivot table??

Originally Posted by tallag

because i dont want the "years", "quarters", or "date" options. plus the Grand Total is no use to me.
but above all, i always have the perception that they're used to change the table to suit what you want to look at - and i dont need that.

i just want 5 top employees based on the sheet 1 stats. and for it to stay like that (obviously if sales numbers change then the ranking changes accordingly).
thats it.

So get rid of the page filters, and the Grand Total. You can format the table to look any way you like, then that's it - it displays exactly what you want, in the way you want. there's no compulsion to do anything more or less with it...

It does exactly as you wanted, you just have to refresh the table when your source data has changed.

Easiest way - use the following code for Sheet2:

Please Login or Register  to view this content.

This will refresh the pivot table every time the worksheet is activated, and automatically keep it updated.

You're welcome.

Pivot table is a great suggestion in this case. If you insist to use formulas we can do this easily using a helper and hidden column.

The only thing to do is refresh the pivot table.

That's to much?

Excel 2007 => Data => Refresh

Using the example sheet of OllyXLS(thank you), do this.

In sheet1! E2 use this formula and copy down. This will be the hidden column.

Formula:

Please Login or Register  to view this content.

In B2 of another sheet, use this.

Formula:

Please Login or Register  to view this content.

In A2, this one.

Formula:

Please Login or Register  to view this content.

Both copy down as you need.

How can i know how your real workbook looks like?

If you want pls upload a small sample workbook for testing showing what have you done until now.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

There is no problem for id to be text or numbers or mixed.

The only that you have to do is to modify(a little) the formula in AF17.

You have

Formula:

Please Login or Register  to view this content.

Do it.

Formula:

Please Login or Register  to view this content.

Notice that as you copy down the bold part of the formula, becomes AD2,AD3,AD4....etc. These are the first instance, then the second etc...

Is it clear now?

You are welcome and thanks for the feed back.