Formula needed please

B

Ben

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?
 
M

Max

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
 
B

Ben

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
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top