Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Pisii
 | Femeie
24 ani
Ialomita
cauta Barbat
24 - 44 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Assuming you have a row B with roughly 1000 entries (or more) in an excel you generate every day, and you want to see how many unique entries you have in column B, you would need either to always write a formula, or to have it inserted in a cell via VBA.
But I repeat, this is good if you will have the same column to count unique values on. Otherwise try post #2 in this topic.
Sub CountUniqueFormula()
Application.ScreenUpdating = False
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
'#### You can either define a cell where to put it... #### CURRENTLY INACTIVE ####
'Range("L2").FormulaLocal = "=SUM(IF(FREQUENCY(MATCH(B2:B" & LastRow & ";B2:B" & LastRow & ";0);MATCH(B2:B" & LastRow & ";B2:B" & LastRow & ";0))>0;1))"
'#### or you can put it in the active cell #### CURRENTLY ACTIVE ####
ActiveCell.FormulaLocal = "=SUM(IF(FREQUENCY(MATCH(B2:B" & LastRow & ";B2:B" & LastRow & ";0);MATCH(B2:B" & LastRow & ";B2:B" & LastRow & ";0))>0;1))"
End With
Application.ScreenUpdating = True
End Sub |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Another approach:
1. select range on which you want to count unique values
2. run the code below
3. select cell where to insert the formula
4. click OK
Sub CountUniqueFormula()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
With ActiveSheet
Set rng = Selection
'cell = rng(rw - 1, col).Address
Set cell = Application.InputBox(prompt:="Select CELL FOR FORMULA:", Type:=8)
On Error Resume Next
If cell Is Nothing Then
MsgBox "cancel pressed"
Else
cell.Cells(1, 1).FormulaLocal = _
"=SUM(IF(FREQUENCY(MATCH(" & rng.Address(0, 0) & ";" & rng.Address(0, 0) & ";0);MATCH(" & rng.Address(0, 0) & ";" & rng.Address(0, 0) & ";0))>0;1))"
End If
End With
Application.ScreenUpdating = True
End Sub |
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
More about LastRow / LastCol
|
|
| pus acum 5 ani |
|