Prev: loop VBA request
Next: Formula ?
From: Seeker on 25 Apr 2010 03:23
I post below earlier on but I couldnt see the thread, if duplicated please
exscute me.

I have 3 columns contain names(unknown, may duplicated and dynamic row),
date and amount; I am looking a way to get a sum and count on data that:
1) date is equal to or greater than today, then
2) loop thru name and subtotal on each customer
3) sum subtotal if value is equal or above 10k
3) count 2) with same name treat as once
Tks

From: Jef Gorbach on 25 Apr 2010 11:40
On Apr 25, 2:29 am, Seeker <See...(a)discussions.microsoft.com> wrote:
> I have 3 columns contain names(unknown, may duplicated and dynamic row), date
> and amount; I am looking a way to get a sum and count on data that:
> 1) date is equal to or greater than today, then
> 2) loop thru name and subtotal on each customer
> 3) sum subtotal if value is equal or above 10k
> 3) count 2) with same name treat as once
> Tks

Give this a look. Wasn't sure of your layout so I used column(a) for
the date, b=client, and c=amount.
It asks the user for a date then copies all rows where the date is
equal/after the user's input to sheet2 where it sorts and subtotals by
client so your original data remains unchanged.

Sub test()
FindDate = CDate(InputBox("Enter date"))
Cells.AutoFilter Field:=1, Criteria1:=">=" & FindDate
Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Cells.AutoFilter 'turn off filter
Worksheets("Sheet2").Activate
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYesr
lastrow = Range("C65536").End(xlUp).Row
Range("A1", "C" & lastrow).Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
From: Rich Locus on 25 Apr 2010 13:43
Seeker:
Your post is not entirely clear.. notice that you have had no responses.
If the customers are not sorted in order, then you will have to loop through
the data once for each customer that fits the criteria.

Since you want totals, I would find a customer, sum them up using your
criteria, and post the customer name and totals to A DIFFERENT worksheet. I
have written similar applications where the solution is just posted to
another worksheet.

For example, you find a date that meets your criteria. Then find every
occurrence of that customer, do the math, then post the totals to another
worksheet, keeping track of the last row posted in the other worksheet. Keep
looping, posting each customer total to the next available row in the other
worksheet.

The programming for this could be somewhat involved, but not too difficult.

Regards,
Rich Locus, Logicwurks, LLC
--
Rich Locus
Logicwurks, LLC


"Seeker" wrote:

> I post below earlier on but I couldnt see the thread, if duplicated please
> exscute me.
>
> I have 3 columns contain names(unknown, may duplicated and dynamic row),
> date and amount; I am looking a way to get a sum and count on data that:
> 1) date is equal to or greater than today, then
> 2) loop thru name and subtotal on each customer
> 3) sum subtotal if value is equal or above 10k
> 3) count 2) with same name treat as once
> Tks
>
 | 
Pages: 1
Prev: loop VBA request
Next: Formula ?