Formula needed please
 

I need to be able to count how many clients are repeated in a record of
attendance sheet. Clients are identified with a unique client number.

I would like to run a macro that searched the record sheet for clients with
multiple entries and copies their details (which are organised across a row)
onto another sheet within the work book. Would also like to total the amount
of money they received.


Date Client Number Voucher# Amount Provided
30/03/2009 66 O0685 $ 65.00
12/12/2008 67 O0392 $ 75.00
9/06/2009 67 P 0021 $ 150.00
22/06/2009 68 P 0686 $ 100.00

In the above example client # 67 is entered twice so I need the macro to
copy that into another worksheet so it looks like this

Client# Number Total
of visits Received
67 2 $225.00


Is this possible?

Formula needed please
 

Try a pivot table, Ben. It'll give you a v.suitable summary in a matter of
seconds.
Create a pivot based on your source table. In Layout, drag n drop Client
Number into the ROW area and DATA area. In DATA, double-click on the field,
set it to Count. Then drag n drop Amount Provided into the DATA area, below
Client Number. Click to finish. In the pivot sheet, drag "Data" and drop it
over "Total" to re-arrange it into columns. That's it. The resulting summary
will look like the below:

Data
CN Count CN Sum Amt
66 1 65
67 2 225
68 1 100
GTotal 4 390

Above any good? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ben" wrote:
I need to be able to count how many clients are repeated in a record of
attendance sheet. Clients are identified with a unique client number.

I would like to run a macro that searched the record sheet for clients with
multiple entries and copies their details (which are organised across a row)
onto another sheet within the work book. Would also like to total the amount
of money they received.


Date Client Number Voucher# Amount Provided
30/03/2009 66 O0685 $ 65.00
12/12/2008 67 O0392 $ 75.00
9/06/2009 67 P 0021 $ 150.00
22/06/2009 68 P 0686 $ 100.00

In the above example client # 67 is entered twice so I need the macro to
copy that into another worksheet so it looks like this

Client# Number Total
of visits Received
67 2 $225.00


Is this possible?

Formula needed please
 

I hate pivot tables max...much rather a macro if possible. Have never used
pivot tables although have tried many times. my brain just doesn't get them

"Max" wrote:

Try a pivot table, Ben. It'll give you a v.suitable summary in a matter of
seconds.
Create a pivot based on your source table. In Layout, drag n drop Client
Number into the ROW area and DATA area. In DATA, double-click on the field,
set it to Count. Then drag n drop Amount Provided into the DATA area, below
Client Number. Click to finish. In the pivot sheet, drag "Data" and drop it
over "Total" to re-arrange it into columns. That's it. The resulting summary
will look like the below:

Data
CN Count CN Sum Amt
66 1 65
67 2 225
68 1 100
GTotal 4 390

Above any good? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ben" wrote:
I need to be able to count how many clients are repeated in a record of
attendance sheet. Clients are identified with a unique client number.

I would like to run a macro that searched the record sheet for clients with
multiple entries and copies their details (which are organised across a row)
onto another sheet within the work book. Would also like to total the amount
of money they received.


Date Client Number Voucher# Amount Provided
30/03/2009 66 O0685 $ 65.00
12/12/2008 67 O0392 $ 75.00
9/06/2009 67 P 0021 $ 150.00
22/06/2009 68 P 0686 $ 100.00

In the above example client # 67 is entered twice so I need the macro to
copy that into another worksheet so it looks like this

Client# Number Total
of visits Received
67 2 $225.00


Is this possible?

Formula needed please
 

No problem. Hang around awhile, its quite possible that vba savvy responders
would jump in here. Alternatively, you could try a fresh thread in
excel.programming. Btw, you should always tailor the subject line to reflect
what you want. Don't re-use the same subject line.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ben" wrote in message
...
I hate pivot tables max...much rather a macro if possible. Have never used
pivot tables although have tried many times. my brain just doesn't get
them