SUMIF Function?

C

Cathy Landry

Hello,

I have 4 columns
A=cardholder
B=trans$
C=monthly limit
D=trans date

Each cardholder could have multiple transaction for a reporting period. I
want to be able to show those cardholders that reach their monthly limits.
My sheet currently has over 11K lines with each cardholder having at least
5-10 trans lines.

Is this possible??

Thank you in advance for any help!
 
K

Ken Hudson

Cathy,
Here is a macro solution.
Right click on any worksheet tab at the botom of your workbook and select
"View Code."
Go to Insert > Module.
Copy the code from below and paste it into your module.
Close the VB editor.
Back in Excel go to Tools > Macro > Macros..., select the macro and click Run.
This macro will sort your data by user and highlight those users who have
reached or exceeded their limit.
Be sure to make a back-up copy of your workbook beforehand.

Sub CheckLimit()
Dim CumTtl As Double
Dim Iloop As Single
Dim NumRows As Single

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

NumRows = Range("A65536").End(xlUp).Row
Range("A1:D" & NumRows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("D1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = 2 To NumRows
If Cells(Iloop, "A") = Cells(Iloop + 1, "A") Then
CumTtl = CumTtl + Cells(Iloop, "B")
Else
CumTtl = CumTtl + Cells(Iloop, "B")
If CumTtl >= Cells(Iloop, "C") Then
Rows(Iloop).Font.Bold = True
CumTtl = 0
End If
End If
Next Iloop

'Turn off warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
 
C

Cathy Landry

Hi Ken,

It didn't sort all of the rows just the cardholders. Did I do something
wrong?
 
K

Ken Hudson

Hi,
Doesn't your data look like this:

Ken $12 $100 7/1/05
Kathy $3 $100 7/23/05
Ken $15 $100 7/24/05
........

If it does, your saying that all rows didn't get sorted or all columns?
 
C

Cathy Landry

Hi Ken,

Yes it does, but when I run the macro it just sort and highlights, but the
other columns didn't sort. So, I have let's say Ken who's monthly limit is
5K, now is showing different monthly limits on his trans$ line. The
cardholders don't have the same monthly limits they range from 500 - 20,000
 
K

Ken Hudson

The macro is written to sort columns A through D. Don't know why it isn't
working.
If possible, please send me a sample of your workbook (maybe 20-30 rows) and
I will see what's going on.

[email protected]
 
Top