Formula needed please

  • Thread starter Thread starter Ben
  • Start date Start date
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?
 
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
 
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
 
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
 
Back
Top