I am trying to configure a spreadsheet for payroll. In column A I have the employee name. In column B I have the amount of hours they worked. I currently have the sheet broken down by job title/ dept that a particular employee works. In column G I have my skilled employees names and have a formula to auto-populate their hours from column B if their names are found in column A (=VLOOKUP(G2,A:B,2,FALSE). I also have a formula in Column D and E that will take anyone with hours greater than 0 from A and B and paste them (=IF(B2>0,B2," ")). I am now looking for a formula that will delete employee name and hours from D and E if they are in columns G and H. An example of the spreadsheet is:
A B C D E F G H
1 Doe, John 8 Doe, John 8 Doe, John 8
2 Doe, Jane 8 Doe, Jane 8 Bee, Max 8
3 Roy, Bill 8 Roy, Bill 8
4 Bee, Max 8 Bee, Max 8
Assuming the formula for populating column D is
=IF(B2>0,A2," ")
one way to accomplish what you want is to amend that formula to read=IF(AND(B2>0,COUNTIFS(G:G,A2)<1),A2," ")
. Similarly amend the formula in column E to read=IF(AND(D2<>" ",B2>0),B2," ")
.Hope this helps.