Macro Needed Please

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

No Pivot Tables Please

 

[Joel]

I did the macro the way I thought would run the fastest. the method I used
is a combination of formulas and VBA. to get the unique clients with more
the one entry I used this formula in sheet 1 and copyied down an unused
column IV

"=COUNTIF(B$2:B2,B2)"

entries with a rusult of 2 are the clients with more than one entry. I then
used autofilter and filtered the result of 2. I then used the specialcells
method to copy the visible cells in column b which are the unique duplicate
clients and put these number in sheet 2. Then used a countif formula in
column 2 to get the counts of entries for each client and used sumif to get
the dollar amounts.




Sub getduplicates()

With Sheets("Sheet1")

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'get count of number of clients from row two to row
'where formula is placed.
'the rows with 2 are the unique client with duplices
'put formula in usused column IV
.Range("IV2").Formula = _
"=COUNTIF(B$2:B2,B2)"
'copy formula down column IV
.Range("IV2").Copy _
Destination:=.Range("IV2:IV" & LastRow)
'put a header in IV1 to make autofilter work properly
.Range("IV1") = "Count"
'autofilter column IV for 2
Range("IV1").Select
.Range("IV1").AutoFilter _
Field:=1, _
Criteria1:="2"

'copy visible client number from column B

.Range("B2:B" & LastRow) _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("sheet2").Range("A2")

'remove autofilter from sheet 1
.Range("IV1").AutoFilter
'remove formulas from column IV
.Columns("IV").Delete


End With

With Sheets("Sheet2")
'add header row to sheet
.Range("A1") = "Client#"
.Range("B1") = "Number of vistis"
.Range("C1") = "Total Received"

'add formula for number of visits in column B
.Range("B2").Formula = _
"=COUNTIF(Sheet1!B2:B" & LastRow & ",A2)"

'add formula for total Received in column C
.Range("C2").Formula = _
"=SumIF(Sheet1!B2:B" & LastRow & ",A2," & _
"Sheet1!D2" & LastRow & ")"

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy formulas down the column
.Range("B2:C2").Copy _
Destination:=.Range("B2:C" & LastRow)

.Activate
.Range("A1").Select
.Columns("A:C").Columns.AutoFit
End With

End Sub

 

[Ben]

Hi Joel

The macro does not work. I get a 400 error

 

[JLGWhiz]

This is the message for a Err 400:

You can't use the Show method to display a visible form as modal. This error
has the following cause and solution:

a.. You tried to use Show, with the style argument set to 1 - vbModal, on
an already visible form.
Use either the Unload statement or the Hide method on the form before
trying to show it as a modal form.

So, since there is no form involved in the code Joel provided, the question
arises, is there something you forgot to include in your original post?

 

[Ben]

The only thing I forgot to say was that the sheet with all the data is call
"Voucher Records". Would that make a difference?

 

[JLGWhiz]

I don't know why you would get a VBA error 400, which is telling you that
you are trying to open a form that is already open. There is nothing in the
code that Joel provided that calls a form to Load or Show. If you click the
Debug button when the error message appears, which line of code is
highlighted?

 

[Ben]

Thanks for your help guys but I think this is bigger than I can handle with
my knowledge.

 

[Joel]

If JLGWhiz is right about the userform then copy the data on the worksheet
into a new workbook. and put my code in the new workbook. My code required
sheets 1 & 2 but you can change the name of the two worksheets in the code to
anything you like.

 

[JLGWhiz]

Ben, don't let it scare you away. This is how you learn about programming.
Take the challenge and learn how to debug the code. Probably the worse
thing that can happen is you spend a couple of hours on it.

 

[Ben]

Thanks again guys...I did copy into a new book with sheet 1 & 2 and it did
work. That mean all I have to do is change references in the macro to sheet 1
& 2 to the actual sheet names and ranges and it should work again. will let
you know.

Cheers