Unique records from a range

[arunsharm82]

Hi,

I have a problem that I cant work out...

I have a spreadsheet that plots out employees names and % worked o
programs (Rows) against a list of programs (Columns).

As the people can work on many programs I need to be able to create
formula that analyses the rows, creates a unique list of names and the
sums up their complete time worked on programs.
Obviously if it sums up to over 100% per employee, then I know th
resources are stretched and we need to hire in more people.

Is there any way of doing this?
I have attached a file that illustrates what I am trying to acheive.

I tried using some formulas that I found on other threads but the
mainly catered for lists that are vertical, not horizontal.

Thanks in advance

Attachment filename: exceltip example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62213

 

[Hans]

Why dont you make a pivot table? Then you very quicly
see,per employee, the amount of time worked and on what
program.

See Data/PivotTable and Pivot Chart report.

Regards,
Hans

 

[arunsharm82]

As easy as it may sound, you cannot produce a pivot table.
Have a look at the attachment and you will see why....

Any other suggestions

 

[Hans]

Hi

I didnt look at your attachment before. You are right, you
cannot make a pivot table with that.

The best thing you can do, i think, is to add a new table
(maybe in a new sheet) that lists all persons working in
Column A, with type of engineer in Column B. You would
have to make this manually.

Then in Column C you enter the following formula for prog
1 (and copies this down):

=IF(ISERROR(VLOOKUP(A1, $K$2:$M$21,3,FALSE)),"", (VLOOKUP
(A1, $K$2:$M$21,3,FALSE)))

in Column D for Prog 2:
=IF(ISERROR(VLOOKUP(A1, $N$2:$P$21,3,FALSE)),"", (VLOOKUP
(A1, $N$2:$P$21,3,FALSE)))

etc for all other prog. The only thing you have to change
is the range.

This formula looks in the range of each Prog to see if it
contains the name mentioned in A1. If the range contains
the name, it goes 3 places to the left and returns the
value in that Cell. If it doesnt, it doesnt return any
value at all ("").

Hope this helps.

Regards,
Hans

 

[Anthony Slater]

Have you tried Data>filter>advance filter?

Copy the names to another column on the same sheet and
then sum up from there?