1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by 919budda, Jan 26, 2018.

  1. 919budda

    919budda New Member

    Messages:
    12
    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

    Attached Files:

  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,348
    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.
  3. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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.
    upload_2018-1-26_22-8-32.png

    Attached Files:

  4. vletm

    vletm Excel Ninja

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

    Attached Files:

  5. 919budda

    919budda New Member

    Messages:
    12

    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.
  6. 919budda

    919budda New Member

    Messages:
    12

    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?
  7. vletm

    vletm Excel Ninja

    Messages:
    3,588
    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?
    Last edited: Jan 29, 2018
  8. vletm

    vletm Excel Ninja

    Messages:
    3,588
    919budda
    You could test this version too...

    Attached Files:

    sathishsusa likes this.
  9. 919budda

    919budda New Member

    Messages:
    12

    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
    GraH - Guido likes this.
  10. 919budda

    919budda New Member

    Messages:
    12

    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.
  11. vletm

    vletm Excel Ninja

    Messages:
    3,588
    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' ...

    Attached Files:

  12. vletm

    vletm Excel Ninja

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

    Attached Files:

  13. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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.
  14. vletm

    vletm Excel Ninja

    Messages:
    3,588
    919budda
    ... could this really solve with Pivot ... hmm?
    Screen Shot 2018-01-30 at 21.50.23.png
    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?

    Attached Files:

  15. 919budda

    919budda New Member

    Messages:
    12

    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.
  16. 919budda

    919budda New Member

    Messages:
    12
    File attached

    Attached Files:

  17. 919budda

    919budda New Member

    Messages:
    12

    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 likes this.
  18. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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 :)
    919budda likes this.
  19. vletm

    vletm Excel Ninja

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

    919budda New Member

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

    vletm Excel Ninja

    Messages:
    3,588
    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 ]!
    Screen Shot 2018-01-31 at 18.29.42.png
    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 <<

    Attached Files:

  22. vletm

    vletm Excel Ninja

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

    Attached Files:

  23. p45cal

    p45cal Well-Known Member

    Messages:
    1,093
    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

    Attached Files:

  24. p45cal

    p45cal Well-Known Member

    Messages:
    1,093
    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.
  25. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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.
    Code (vb):

    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
     

    Attached Files:

Share This Page