Solved Find same work item, same employee, multiple approvals

Microsoft Office 2010 professional
September 13, 2013 at 07:54:50
Specs: Windows XP
I don't know how else to say that because I'm not sure what I should be using for this Excel issue. The issue is finding out if the same employee is pushing through a work item by approving it multiple times. The spreadsheet created shows the work ID (col A), the company name (col B), and the employee ID (col C-which might be one of three types of ID).

Each time the work ID shows, it means it was approved for a single step in the process. There are multiple levels of approval and each one should be done by a different area/employee, so there should be NO duplicates on employee ID.

How do I do this? I think the first step would be to change all entries on col C to be the same, like either all names or all short IDs or long IDs - the three types of possibilities. Then I'd compare A2 to anything else on column A and if I find another match, compare C2 to the match's -C.

Is it possible to do this in a single formula? Do I need a macro? I know nothing of pivot tables but would that be useful here?

As always, any input would be greatly appreciated!


See More: Find same work item, same employee, multiple approvals

Report •

#1
September 13, 2013 at 08:41:20
Here is a Conditional Formatting solution that should work for you.
You did not specify which version of Excel you are using, this is for Excel 2007, as it uses the =COUNTIFS() function.

If your data looks like:

        A        B         C
 1)  Work ID  Compnay   Employee
 2)  1        alpha     ab123
 3)  2        beta      ab124
 4)  3        gamma     ab125
 5)  1        delta     ab126
 6)  5        epsilon   ab123
 7)  6        zeta      ab128
 8)  7        eta       ab129
 9)  1        alpha     ab123
10)  9        iota      ab131
11)  10       kappa     ab123

Conditional Formatting 2007

1) Select your cell or range of cells, IE A2 thru C11
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=COUNTIFS($A$2:$A$11,$A2,$C$2:$C$11,$C2)>1

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

In the above data, you should have two rows highlighted,
rows 2 and 9, as they both have the same Work ID and Employee ID.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#2
September 13, 2013 at 09:02:35
✔ Best Answer
Another option, using a modified version of Mike's formula would be as follows:

I basically started with Mike's input data and made sure that there a couple of duplicate approvals (Work ID's 1 & 7).

I then used the Advanced Filter feature to create a list of unique Work ID's in another column, e.g. D:

        A        B         C           D
 1)  Work ID  Company   Employee    Work ID
 2)  1        alpha     ab123          1
 3)  2        beta      ab124          2
 4)  3        gamma     ab125          3
 5)  7        delta     ab126          7
 6)  5        epsilon   ab123          5
 7)  6        zeta      ab128          6
 8)  7        delta     ab126          9
 9)  1        alpha     ab123         10
10)  9        iota      ab131
11)  10       kappa     ab123

Now place this Formula in E2 and drag it down.

=IF(COUNTIFS($A$2:$A$11,$D2,$C$2:$C$11,$C2)>1,C2 & " Is A Bad Employee","")

You should end up with this:

        A        B         C           D             E
 1)  Work ID  Company   Employee    Work ID    Multiple Approvals
 2)  1        alpha     ab123          1     ab123 Is A Bad Employee
 3)  2        beta      ab124          2
 4)  3        gamma     ab125          3
 5)  7        delta     ab126          7     ab126 Is A Bad Employee
 6)  5        epsilon   ab123          5
 7)  6        zeta      ab128          6
 8)  7        delta     ab126          9
 9)  1        alpha     ab123         10
10)  9        iota      ab131
11)  10       kappa     ab123

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
September 13, 2013 at 11:23:16
LOL! I loved your solution, DerbyDad, but ended up using Mike's idea. Thanks to both of you for your time.

Report •
Related Solutions


Ask Question