Formula/Alternate Method for Flagging Documents Created and Approved by Same User

[919budda]

Good afternoon,

I'm at a loss for thinking of a formula or an easy way to flag suspicious travel authorizations. What I want to flag are the travel authorizations that have a Status of Created, Authorization Signed, and Reviewed, all within the same Authorization Number and by the same Acting Employee ID. The flagging could be another column with an indicator or a separate list of authorization numbers where this occurs. I'm open to any methods - formula, pivoting the data, vba, etc.

In the sample file, you'll see that Travel Authorization E2M needs to be flagged because it has Created, Reviewed, and Signed all by the same individual (Employee 266).

Any ideas are appreciated. Thanks,
~josh

 

[Luke M]

As a start, you could have a formula like this in H2:

=COUNTIFS(B:B,B2,F:F,F2,C:C,C2)>2

It's checking to see if the same travel authorization had matching employee/approver for more than 2 of the 4 items.

 

[GraH - Guido]

Hi 919budda, since you mentioned being open to suggestions. I believe there is a rather easy way to accomplish this with a Power Query. Which version of Excel are you running? 2010/2013/2016?
Be assured, some wizard will come up with formula, vba... solutions as well.

 

[vletm]

919budda
... or press [ Do It ]
and
You'll get green marks (with yellow bonus).

 

[919budda]

As a start, you could have a formula like this in H2:

=COUNTIFS(B:B,B2,F:F,F2,C:C,C2)>2

It's checking to see if the same travel authorization had matching employee/approver for more than 2 of the 4 items.

Thanks Luke. I had initially approached this as a Countifs problem. Unfortunately, the formula will return a True value for authorizations that dont meet the criteria. For example, this reports a True value for users that Created, Updated, and Signed their own authorizations even though they had a separate employee perform the Review.

 

[919budda]

919budda
... or press [ Do It ]
and
You'll get green marks (with yellow bonus).

Vletm, The code looks great, and I think I follow the logic of it. However, if I try to run the code on more than a small amount of data my excel freezes. About 200 lines of data appears to be the limit, although I have about 35,000 lines to go through. Any settings I could change on my local machine to keep it running?

 

[vletm]

919budda
Many things would be possible ...
Could You sent more realistic sample file ... 500-1000 lines?
then it would be possible to test more.
> and how would You flag those? Ex You would see ONLY those flagged lines?

 

[vletm]

919budda
You could test this version too...

 

[919budda]

Hi 919budda, since you mentioned being open to suggestions. I believe there is a rather easy way to accomplish this with a Power Query. Which version of Excel are you running? 2010/2013/2016?
Be assured, some wizard will come up with formula, vba... solutions as well.
View attachment 49309

GraH-Guido, Thanks for your solution. I don't have much in the way of PowerPivot/Query knowledge, so I'm very glad you suggested it. It forces me to figure out what exactly you did to get to the end outcome.

To answer your question, I'm using Excel 2013 but I'm created a process for a co-worker that is running Excel 2016.

Give me some time to digest what you have going on in the workbook and I'll spot check some of the results over the larger population and see how it fares.

919budda

 

[919budda]

919budda
You could test this version too...

I ran two tests so far and with 200 lines it was 25 seconds. 400 lines was 3:02.

I will run some additional tests and see how it does. Along your same lines of thinking I will see if I can reduce lines from the overall population, maybe excluding some that have a current status that has not yet made it to the Signed phase.

Thanks for all your help with this.

 

[vletm]

919budda
What were 1st version times?
So far, I could only add few lines ...
but without data, it's challenge!
This version should be 'faster' ...

 

[vletm]

919budda
... faster version.
With dummy data ~4000 rows ... takes time
but
You'll see progress ...
With real data, this could work better.

 

[GraH - Guido]

If speed is a issue. I did a test with about 11K rows. Power Query does it under 10s. Given my dummy data was perhaps too easy. Would love to hear how PQ is handling the real data.
919budda, you could test this if you just paste your real data in the table in the file I uploaded en press refresh on the output table (right mouse click).
With view queries/show query pane on, you should see how fast it goes and if it returns errors or not.

 

[vletm]

919budda
... could this really solve with Pivot ... hmm?

anyway, with 'my sample data' (( 280 * original sample )) it shows ...
Change 'sample data' with 'real data' and check 'Pivot'
if more 'TAN's then copy more right side formulas down ... hmm?

 

[919budda]

919budda
... could this really solve with Pivot ... hmm?
View attachment 49392
anyway, with 'my sample data' (( 280 * original sample )) it shows ...
Change 'sample data' with 'real data' and check 'Pivot'
if more 'TAN's then copy more right side formulas down ... hmm?

It does work better. The original version wouldn't even finish. This last version now does so in a few minutes. Thanks for the solution and the innovative approach.

I'm attaching a "full size" file of dummy data. It took me a while to randomize the data while keeping it coherent. I couldn't upload all rows because of the upload limit, but you get the idea.

 

[919budda]

File attached

 

[919budda]

If speed is a issue. I did a test with about 11K rows. Power Query does it under 10s. Given my dummy data was perhaps too easy. Would love to hear how PQ is handling the real data.
919budda, you could test this if you just paste your real data in the table in the file I uploaded en press refresh on the output table (right mouse click).
With view queries/show query pane on, you should see how fast it goes and if it returns errors or not.

You are correct - PowerQuery handles it fast. I did run into a few errors caused by a small number of rows with blank fields in the 'Status' column. I'll need to work with my user to understand what these are. For now, I removed them and ran a test and the PowerQuery works. Thanks for opening my mind to a new way of analysis.

 

[GraH - Guido]

Wonderful, and I did not even bother to optimize the steps. Now the errors could be because the PQ I designed does not "manage" the data pattern correctly, since it was sample data and probably not all scenarios were present. But it can be adjusted. The basics seems to be covered.
So thank you for the feedback and kind words,919budda. Appreciated

 

[vletm]

919budda
I would like to know
how many authorization numbers would be 'Your needed answer'?
My the latest 'solution' finds 26, within two seconds.

 

[919budda]

919budda
I would like to know
how many authorization numbers would be 'Your needed answer'?
My the latest 'solution' finds 26, within two seconds.

The power query returned 25 records. If you post your solution, I can compare and see which one is correct.

 

[vletm]

919budda
Okay ...
I did two files
a) only results (CREATED-rows)
b) solution WITHOUT that DATA
= You gotta copy data if You would like to test it > press [ Flags ]!

There is also possible to 'scroll rows'.
... maybe one time should ReFresh Pivot-table MANUALLY.
>> From Pivot-table You can see few cases ... just learn to read <<

 

[vletm]

919budda
This is without Pivot ... hmm ... 'manually solving'
And it takes ~four seconds.
Bases from my original sample.
This has Your full data!

 

[p45cal]

Taking a leaf from vletm's pivot table idea in msg#14, the attached has a macro, run by clicking the Update button on Sheet1, which takes about 1 second to produce a new sheet with a table of potential offenders from the 20k+ rows of data.
There's already a sheet43 which is the result, but can be deleted.
You just need to replace the raw data on sheet1, keeping the same headers (but not necessarily in the same order), with new data, then click the button

 

[p45cal]

Further to my last message, a development to allow the potential offenders to be listed/seen easily within the raw data.
The attached does the same as the last file, but also moves the results to the same Sheet1, in columns K:M. Additionally there are 2 more buttons and a scroll bar. This filters the raw data according to one row in column K (see cell I3 for which row is being looked at). The scrollbar changes which row is being looked at.

 

[GraH - Guido]

Witnessing some impressive continuous improvement by Vletm and p45cal ...
I like that.

Since we now understand the use case a bit better, here is the updated M-code, takes also about a second and generates only the list with records where the alert is true.

let
  Source = Excel.CurrentWorkbook(){[Name="tSource"]}[Content],
  FilterStatus = Table.SelectRows(Source, each ([Status] = "AUTHORIZATION SIGNED" or [Status] = "CREATED" or [Status] = "REVIEWED")),
  ChangeType = Table.TransformColumnTypes(FilterStatus,{{"Employee", type text}, {"Employee ID", type text}, {"Travel Authorization Number", type text}, {"Acting Employee", type text}, {"Acting Employee ID", type text}, {"Status", type text}, {"Status Date/Time", type datetime}}),
  PivotStatus = Table.Pivot(ChangeType, List.Distinct(ChangeType[Status]), "Status", "Acting Employee ID"),
  FilledUp = Table.FillUp(PivotStatus,{"AUTHORIZATION SIGNED", "REVIEWED"}),
  FilterNull = Table.SelectRows(FilledUp, each ([CREATED] <> null)),
  AddAlert = Table.AddColumn(FilterNull, "Alert", each if [CREATED]=[REVIEWED] and [REVIEWED]=[AUTHORIZATION SIGNED] then true else false),
  FilterAlert = Table.SelectRows(AddAlert, each ([Alert] = true))
in
  FilterAlert