Excel programming - scanning values, populating cells - please hel

[Guest]

Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with the
company names that have a credit risk rating which is above average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick

 

[Bernie Deitrick]

Nick,

The best solution would be to insert a column of formulas

=B2>(AVERAGE(B:B)+STDEV(B:B))

and then use Data Filter Autofilter and select TRUE for that column.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks for this Bernie, however, I'm looking to code a button that will do
this automatically, then return the values into a separte worksheet without
having to touch the source data. I have over 6000 entries so I'm worried that
autofilter may crop some values or lead to other data handling problems.
Essentially I have to present the workbook in such a way that a total novice
can filter the values. Any ideas at all?

Much appreciated, Nick

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim j As Long
Dim iLastRow As Long
Dim nAve As Double
Dim nStdev As Double

nAve = Application.Average(Worksheets("Data").Columns(2))
nStdev = Application.StDev(Worksheets("Data").Columns(2))
With Worksheets("Anomalies")
.Range("A1").Value = "Company"
.Range("B1").Value = "Rating"
.Range("C1").Value = "Above Ave?" & vbLf & "(" & Format(nAve,
"#,##0.00") & ")"
.Range("D1").Value = "Above STDev?" & vbLf & "(" & Format(nStdev,
"#,##0.00") & ")"
End With

j = 1
With Worksheets("Data")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "B").Value > nAve Or .Cells(i, "B").Value > nStdev
Then
j = j + 1
Worksheets("Anomalies").Cells(j, "A").Value = .Cells(i,
TEST_COLUMN).Value
Worksheets("Anomalies").Cells(j, "B").Value = .Cells(i,
"B").Value
End If
If .Cells(i, "B").Value > nAve Then
Worksheets("Anomalies").Cells(j, "C").Value = "Y"
End If
If .Cells(i, "B").Value > nStdev Then
Worksheets("Anomalies").Cells(j, "D").Value = "Y"
End If

Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernie Deitrick]

Nick,

You could use the same formula, and then use a Pivot Table based on the data set but on another
sheet, and use the column with the formula as the first row field, set to TRUE - otherwise, try Bob
Philip's macro.

HTH,
Bernie
MS Excel MVP