Excel Help - How to delete duplicates from multipe columns

0
votes

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
1
excelformula

1 Answers

0
votes

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.