ExcelBanter - SUMIF Function?

Hi Ken,

I sent a sample to your email listed below.

Thank you
Cathy

"Ken Hudson" wrote:

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.

--
Ken Hudson

"Cathy Landry" wrote:

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

"Ken Hudson" wrote:

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?

--
Ken Hudson

"Cathy Landry" wrote:

Hi Ken,

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

"Ken Hudson" wrote:

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

--
Ken Hudson

"Cathy Landry" wrote:

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!