|
Code for unique Data Entries?
I have a macro that I seek to apply only on unique entries. How 2 achieve the
same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
Right click the sheet tabView code and paste the below code...and try
entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
Sorry Jacob,
But the code is not working. Don't you think a loop should be used to gather-up statistics pertaining to unique entries? -- Best Regards, Faraz "Jacob Skaria" wrote: Right click the sheet tabView code and paste the below code...and try entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
Another example is how to have filter be applied upon a data set but only the
times a unique entries are present? -- Best Regards, Faraz "Jacob Skaria" wrote: Right click the sheet tabView code and paste the below code...and try entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
Paste the code and in Column A try entering duplicate values..
If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Another example is how to have filter be applied upon a data set but only the times a unique entries are present? -- Best Regards, Faraz "Jacob Skaria" wrote: Right click the sheet tabView code and paste the below code...and try entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
|
Code for unique Data Entries?
That is the main issue!
I don't seek to carry out an operation later on, but rather apply the same on an existing data set. For eample, with a data list in A:A how to have the same filtered out with different criteria to extract the records pertaining to each of the unique entry? -- Best Regards, Faraz "Jacob Skaria" wrote: Paste the code and in Column A try entering duplicate values.. If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Another example is how to have filter be applied upon a data set but only the times a unique entries are present? -- Best Regards, Faraz "Jacob Skaria" wrote: Right click the sheet tabView code and paste the below code...and try entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
Code for unique Data Entries?
Thanx Bernd but it was just an example.
How to have a data filtered for each unique entry of a column? -- Best Regards, Faraz "Bernd P" wrote: Hello Faraz, I suggest to take my UDF Lfreq: http://sulprobil.com/html/listfreq.html Regards, Bernd |
Code for unique Data Entries?
Insert a new module and paste the below code...Run Sub Macro and see...This
will work on the active sheet Col A from Row1....Try and feedback Dim arrTemp As Variant Sub Macro() Dim lngRow As Long Dim varData As Variant ReDim arrTemp(0) For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("A" & lngRow) < varData Then AddtoArray Trim(Range("A" & lngRow)) End If varData = Range("A" & lngRow) Next For lngRow = 1 To UBound(arrTemp) varData = WorksheetFunction.CountIf(Columns("A"), arrTemp(lngRow)) MsgBox "There were " & varData & " entries of " & arrTemp(lngRow) Next End Sub Sub AddtoArray(varTemp As Variant) Dim lngTemp As Long For lngTemp = 1 To UBound(arrTemp) If arrTemp(lngTemp) = varTemp Then Exit Sub Next ReDim Preserve arrTemp(UBound(arrTemp) + 1) arrTemp(UBound(arrTemp)) = varTemp End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: That is the main issue! I don't seek to carry out an operation later on, but rather apply the same on an existing data set. For eample, with a data list in A:A how to have the same filtered out with different criteria to extract the records pertaining to each of the unique entry? -- Best Regards, Faraz "Jacob Skaria" wrote: Paste the code and in Column A try entering duplicate values.. If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Another example is how to have filter be applied upon a data set but only the times a unique entries are present? -- Best Regards, Faraz "Jacob Skaria" wrote: Right click the sheet tabView code and paste the below code...and try entering data into Col A Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Count = 1 And Trim(Target.Text) < "" Then If WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) 1 Then MsgBox WorksheetFunction.CountIf(Columns(Target.Column), Target.Text) & " entries of '" & Target.Text & "'" Target = "" End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: I have a macro that I seek to apply only on unique entries. How 2 achieve the same. For instance what would be a sample code for: 1) Entering a data column; & 2) Result being message/alert box appearing as many times as there are UNIQUE entries, like: "There were 50 entries of A" "There were 45 entries of B" "There were 59 entries of C" "There were 71 entries of D" if there was a record set, as selected, with 229 records/rows, but with only 4 unique entries. -- Best Regards, Faraz |
| All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com