#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need forumla for percentage | mquillen | Excel | 5 | 02-06-2017 08:59 AM |
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 |