Assign single number to duplicate values in column

[excelresumes]

Hey guys, I have another question with my scheduling book for my non-profit company.

Right now it's set up so Clients are in Column A and the staff for each client is listed in columns B through H.

Next to that is Clients in Column J and the hours they are in program. So the Staff are listed again in Column S and the formula in T adds up their names corresponding to the hours. This all works great thanks to some previous help on this forum:
http://www.excelforum.com/excel-form...om-sheet2.html


Now the new issue we have is that Staff can possibly work with multiple Clients on the same day and even though it's still a 6 hour day for example, it's counting as 12 or 18 hours.

Is there a way to stop this from happening? Like if the column Monday detects the same name multiple times, we only want it to count for one.

Even if we have to create another row/column just for the staff that have duplicate names... I have no idea what to do here.

In the attachment:

I have conditional formatting set individually for Monday - Sunday to highlight duplicate names, Right now we have Staff "Josh" on Monday working with Clients "Steve Bolhman" and "Mark Reign" Both clients are 6 hours for the day, and right now Josh is getting 12 hours instead of 6.

We also have Staff "Ray" working with Clients "Walter Thomas" and "Bethany Timms" who are both 8 hour clients, and Ray is being paid for 16 hours instead of 8.

If you need any clarifications please let me know. Thank you all for your help and time.

-Steven

[JBeaucaire]

If you DIDN'T have people doing doubles, then the formula in T2 could be simply:

=SUMPRODUCT(--($B$2:$H$11=S2), $K$2:$Q$11)


I'll have to see if something more elegant comes to mind, but to get JUST the first value of each shift worked on each day, use VLOOKUP. IFERROR can trap the days they don't work at all.

=IFERROR(VLOOKUP(S2, $B$1:$K$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $C$1:$L$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $D$1:$M$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $E$1:$N$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $F$1:$O$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $G$1:$P$11, 10, 0),0)+IFERROR(VLOOKUP(S2, $H$1:$Q$11, 10, 0),0)

[DonAx]

SI have a table with codes of the countries in Sheet2, of course it is bigger. In Sheet1 I have some name of the countries that are without codes.....please help me to fill in the column B (Sheet1) with data matched in Sheet2.
Thanks

[FDibbins]

DonAx....Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[lugburz]

client_staff_duplicates.xlsx

find attached file. if your staff works with 2 or more clients, i took the maximum hours from them. i used array formulas, it should be enterd by pressing ctrl+shift+enter.

[excelresumes]

Thanks for the help guys! lugburz: I'm looking at yours right now, can you explain how you did this? I'm not familiar with array formulas, are you saying by entering a name with crtl+shift+enter it will automatically use only the largest number?

Thanks in advanced.

[excelresumes]

Just wanted to update, it's working great! Thanks again everyone, I finally understand it and have it figured out again yay!