Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2017, 07:32 AM
chaserwill chaserwill is offline Percentage of . . . Windows 10 Percentage of . . . Office 2016
Novice
Percentage of . . .
 
Join Date: Mar 2017
Posts: 1
chaserwill is on a distinguished road
Default Percentage of . . .

Looking at purchasing card data for my employer. A red flag for potential abuse of the program is a cardholder using a specific vendor a high percentage of time.



For example, let's say one month of p card data has 2,000 separate transactions (cardholder name, vendor name, amount, post date, etc.). John Doe accounts for 100 of these transactions and of these 100, he's used ABC Co. 95 times.

What would be the most efficient and effective formula/logic that can be applied to the entire spreadsheet that would "red flag" this cardholder as a "specific vendor - high percentage" user so his activity can be further examined?
Reply With Quote
  #2  
Old 03-10-2017, 09:28 AM
Logit Logit is offline Percentage of . . . Windows 10 Percentage of . . . Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Code:
Option Explicit
'This macro is designed to count the # of occurences in Col A. To change for a different column,
'every instance referencing Col A should be edited to reference the new column.
Sub Special_Countif()
Dim i, LastRowA, LastRowB
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Columns("B:C").ClearContents
    For i = 1 To LastRowA
        If Application.CountIf(Range("B:B"), Cells(i, "A")) = 0 Then
            Cells(i, "B").Offset(1, 0).Value = Cells(i, "A").Value
        End If
    Next
        Columns("B:B").Select
        Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        LastRowB = Range("B" & Rows.Count).End(xlUp).Row
        For i = 2 To LastRowB
        Cells(i, "C").Value = Application.CountIf(Range("A:A"), Cells(i, "B"))
    Next i
    Range("B1").Value = "Entry"
    Range("C1").Value = "Occurrences"
    Range("B1:C1").HorizontalAlignment = xlCenter
    Range("B1").Select
    Columns("B:C").AutoFit
    Application.EnableEvents = True
End Sub
Column A = Badge user's ID
Column B = Consolidated list of badge user's (after macro is run)
Column C = Total # times each badge was used as displayed in Col A

Col C has been Conditionally Formatted to highlight in RED any Badge ID that exceeds being used >10 times.

All of the above can be edited to suite your needs.
Attached Files
File Type: xlsm Count Name Occurrences.xlsm (18.6 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentage of . . . Need forumla for percentage mquillen Excel 5 02-06-2017 08:59 AM
Percentage of . . . overall percentage ketanco Project 1 08-04-2012 05:56 AM
Segmentation with percentage yazen PowerPoint 0 05-01-2012 07:55 AM
percentage increase? dgardner Excel 2 07-13-2011 03:04 PM
percentage on columns 911 Excel 1 09-01-2010 09:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft