Solved: Filter data and count unique

[U_Shrestha]

In the attached sheet("AuditIssues"), there is one button that says, "Analyze Data". Clicking it brings up the userform "Active Issues Analysis"; it has one combo box and 2 text values. I need help with following:
1) Combo box: In the choose period combo box, I want to be able to choose 4-types of date (issues that are 0 to 30 days old from today, 31 to 60 from today, 61 to 90, 91 to 120 from today and more than 121 days from today BASED ON COLUMN B AND the value "Active" from column H.

2) Text Box1: I want to count "Active" from column H based on the filtered data.

3) Text Box 2: Count no. of unique numbers from column A.

I need to make the range in column A, B and H to be dynamic, I don't know how to set the dynamic ranges in macro. Can some please help me? Thanks.

[Ago]

dynamic range can be solved with this line.

[vba]
nA = Cells(Rows.Count, 1).End(xlUp).Row ' last cell column A
nB = Cells(Rows.Count, 2).End(xlUp).Row ' last cell column B
nH = Cells(Rows.Count, 8).End(xlUp).Row ' last cell column H
[/vba]

that gives you the last cell that has a value. so now you know the last cell is nA and your first cell is A3, range fixed.


3) can the same number be at "diffrent places" or is the list always sorted the way it is now?
or could the order be:
6
6
6
7
7
8
6


2)

[VBA]
Set myRng = Range("H3", "H" & nH) 'sets the range in myRng
For Each mycell In myRng.Cells
If mycell.Value = "Active" Then
Active = Active + 1
End If
Next mycell
[/vba]
this will give you the number of actives in H column.
now you just need to use textbox1.value = Active or something.

its bedtime now.

[herzberg]

3) Text Box 2: Count no. of unique numbers from column A.

Here's something you can do to count for unique values:
[vba]With ActiveSheet
.Columns(1).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
TextBox2.Text = WorksheetFunction.CountA(.Columns(1).SpecialCells(xlCellTypeVisible)) - 1
.ShowAllData
End With[/vba]

[U_Shrestha]

Thank you both for the responses, I will try the codes and let you know. As far as the numbers are concerned it will be scattered throughout the column.

My main concern is, how do I filter the columns based on the value chosen in the combo box? Is this possible? Where do I put the codes.

[herzberg]

Depending on you want it to be done, you can place the code under the Change event of the combo box, Click event of a command button, etc. It depends on when and how you want to the code, i.e. the filtering, to be triggered.

[U_Shrestha]

I tried to follow your advice but since I don't know VB I couldn't do it, I would really appreciate if you can take a look. Here's what I have done so far.

1)I added a helper column "O" that shows how many days has it been since the inspection date (Column B), so the values would be "0-30", "31-60", "31-90", "91-120" and ">120" in Column "O" based on corresponding value in column "B"(inspection date).

2) Now, what I am looking for is, when user clicks on button "Analyze Data", and select a period from combo box, e.g "Active issues for zero to 30 days" then in the text box that says "No. of Active Issues", I want a macro to trigger that will autofilter "Active" from Column "H" and "0-30" from column "O"; and then count "Active" from column "H" from visible rows. The autofilter should work in a similar way based on the selected period (31-60 days, 61 to 90 days and give the count of active issues in the text box).

3) In other text box that reads "No of stations with active issues", I want the count of unique no. from column A (beginning from A3 downwards) in the text box. Can you please help? Thanks.

[U_Shrestha]

Please disregard my previous attachments as I decided to use combobox and text box directly in the worksheet. Now, I used some help columns to filter data based on chosen period from the combo box.

Now I need following help:

1) I used following code to populate the combobox. My problem is, the combobox adds the list repeatedly in the combobox each time I activate the sheet. I just need the items to appear only once, not reproducing itself each time I activate it.
[vba]Private Sub Worksheet_Activate()
With ComboBox1
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
End Sub[/vba]
(2) In textbox1, I want to have a COUNT OF UNIQUE NUMBERS FROM COLUMN A after the filter is applied or it should always show the count.

(3) In textbox2, I want to have a count of the word "Active" from COLUMN H each time the filter is applied.

Any help would be appreciated. Thanks.

[U_Shrestha]

Is it possible to have a count of unique numbers from a row in a text box whenever autofilter is triggered? The textbox is embedded from control toolbox directly in the worksheet. Thanks.

[herzberg]

My problem is, the combobox adds the list repeatedly in the combobox each time I activate the sheet. I just need the items to appear only once, not reproducing itself each time I activate it.

That's simple; clear the combo box before populating it then, e.g. :
[vba] Private Sub Worksheet_Activate()
With ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
'Etc...
End With
End Sub[/vba] In this way, the combo box will not continue appending items to the bottom.

As for the rest, check out the attachment. Too lengthy to type all out here.

[Krishna Kumar]

Hi,

I used following code to populate the combobox. My problem is, the combobox adds the list repeatedly in the combobox each time I activate the sheet. I just need the items to appear only once, not reproducing itself each time I activate it

Replace your old code with the following

[vba]Private Sub Worksheet_Activate()
Dim cbList
cbList = Array("Active Issues 0 to 30 days old", "Active Issues 31 to 60 days old", _
"Active Issues 61 to 90 days old", "Active Issues 91 to 120 days old", _
"Active Issues >120 days old", "All Active Issues")
With Me.ComboBox1
.Clear
.List = Application.Transpose(cbList)
End With
End Sub[/vba]

For unique count, try,

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A3:A200,ROW(A3:A200)-ROW(A3),,1)),MATCH(A3:A200,A3:A200,0)),ROW(A3:A200)-ROW(A3)),1))

Array formula. Link this formula cell to textbox1

For 'active' count,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A3:A200,ROW(A3:A200)-ROW(A3),,1)),--(H3:H200="Active"))

Again link this cell.

HTH

[U_Shrestha]

Hello Herzberg,

You gave the perfect solution to me. Thank you very much.

Thanks Krishna, I was looking for the solution in VB.

[U_Shrestha]

Hi,

I need some more modifications in the code as described below:

1) For some reason, I always need to keep the autofilter mode in the sheet. So I think following code needs to be modified. As for the textbox values, can it always show the counts of "Active" as wells as "Station Numebrs"?
[vba]Private Sub CommandButton1_Click()
With Me
If .AutoFilterMode = True Then
.AutoFilterMode = False
TextBox1.Text = ""
TextBox2.Text = ""
End If
End With
End Sub[/vba]
2) I need a MsgBox when any macro chosen from CmbBox results in No Data. Example. Selecting "Active Issues 61 to 90 days old" result in zero data. The message should say "No data". One of the code I am using to call macro from combobox selection is posted below. How can this code be modified?
[vba]Sub ActiveSixtyOneToNinety()
'
' ActiveZeroToThirty Macro
' Macro recorded 4/16/2008 by ushrestha'
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
Selection.AutoFilter Field:=16, Criteria1:="Active61-90"
Application.ScreenUpdating = True
End Sub[/vba]
I do not know how to remove "Solved" in the headline. I would really appreciate your help. Thanks.

[tstav]

One quick note I can make is about the AuditIssues code module.

In the Sub ActiveNinetyOneToHundredTwenty()
you have Criteria1:="Active>120"

and in Sub ActiveGreaterThanHundredTwenty()
you have Criteria1:="Active91-120"

Seems to me the Criteria are in the wrong Subs.

I'll keep looking...

[U_Shrestha]

Thanks.