Count unique entry in a cell & column-vb scr
Closed
hossain.iqbal
Posts
3
Registration date
Saturday February 21, 2009
Status
Member
Last seen
February 22, 2009
-
Feb 21, 2009 at 10:20 PM
hossain.iqbal Posts 3 Registration date Saturday February 21, 2009 Status Member Last seen February 22, 2009 - Feb 22, 2009 at 10:52 PM
hossain.iqbal Posts 3 Registration date Saturday February 21, 2009 Status Member Last seen February 22, 2009 - Feb 22, 2009 at 10:52 PM
Related:
- Count unique entry in a cell & column-vb scr
- Vb editor download - Download - IDE
- If cell contains specific text then return value in another cell ✓ - Excel Forum
- Count sentences ✓ - Programming Forum
- Excel count how many times a value appears in a column ✓ - Office Software Forum
- Nokia.mobi/entry/van/main/c7-00 - Phones, PDA & GPS Forum
2 responses
Hello!
I had to wait for an hour to try it as I was on HWY 401. This is exactly what I wanted to do!! I cant thank enough!
T H A N K Y O U!
I had to wait for an hour to try it as I was on HWY 401. This is exactly what I wanted to do!! I cant thank enough!
T H A N K Y O U!
You can check out the file from the link and see if that is what you are looking for.
Just click on the button "Unique Entries" on the sheet.
[URL=http://www.4shared.com/file/89009069/45098cbb/SDR.html]SDR.xls[/URL]
Here is the code:
Private Sub CommandButton1_Click()
Dim start As Range
Set start = Range("A1")
Dim i
i = 2
Do Until Range("A" & i) = ""
dup = i + 1
If Range("A" & i) <> start Then
If Range("A" & dup) <> Range("A" & i) Then
Count = Count + 1
If Len(Range("A" & i)) > 8 Then
If Left(Range("A" & i), 8) <> start And Right(Range("A" & i), 8) <> start Then
Count = Count + 1
End If
End If
End If
End If
i = i + 1
Loop
MsgBox "Total new SDR found = " & Count
End Sub
Just click on the button "Unique Entries" on the sheet.
[URL=http://www.4shared.com/file/89009069/45098cbb/SDR.html]SDR.xls[/URL]
Here is the code:
Private Sub CommandButton1_Click()
Dim start As Range
Set start = Range("A1")
Dim i
i = 2
Do Until Range("A" & i) = ""
dup = i + 1
If Range("A" & i) <> start Then
If Range("A" & dup) <> Range("A" & i) Then
Count = Count + 1
If Len(Range("A" & i)) > 8 Then
If Left(Range("A" & i), 8) <> start And Right(Range("A" & i), 8) <> start Then
Count = Count + 1
End If
End If
End If
End If
i = i + 1
Loop
MsgBox "Total new SDR found = " & Count
End Sub
hossain.iqbal
Posts
3
Registration date
Saturday February 21, 2009
Status
Member
Last seen
February 22, 2009
Feb 22, 2009 at 09:39 PM
Feb 22, 2009 at 09:39 PM
just one little thing that i dont know how to fix ..
It seems like it is picking up a "new entry" if i have a space after my entry. For examle "SDR343" would give one count and "SDR343 " as two entries.
is there anyway we can fix that?
thanks in advance again for your greate help!!!
It seems like it is picking up a "new entry" if i have a space after my entry. For examle "SDR343" would give one count and "SDR343 " as two entries.
is there anyway we can fix that?
thanks in advance again for your greate help!!!
hossain.iqbal
Posts
3
Registration date
Saturday February 21, 2009
Status
Member
Last seen
February 22, 2009
Feb 22, 2009 at 10:52 PM
Feb 22, 2009 at 10:52 PM
I think i am really close to fixing it ...... but I am facing a stumbling block right now. The code is taking each cell as a whole entry. what i need to do is take each SDR as a individual entry and then compare with each one in that column. below example basically gives a total count of 8 where as it should give total count of 5.
Please see below for the code. (I kept pretty much the same code as you gave me). My SDR right now has 6 digits.
Data below in my column A:
+++++++++++++++++++++++++++++++++++
SDR344678, SDR676856
SDR790657
SDR676856
SDR344678
SDR583850
SDR748393
END
+++++++++++++++++++++++++++++++++++++++++++
CODE:
=====================
Private Sub CommandButton1_Click()
Dim start As Range
Dim total_count As Range
Set start = Range("A1")
Set total_count = Range("B1")
Dim i
i = 2
Do Until Range("A" & i) = "END" 'I added a given condition to end the loop, since I will have empty cells
dup = i + 1
If Range("A" & i) <> start Then
If Range("A" & dup) <> Range("A" & i) Then
Count = Count + 1
If Len(Range("A" & i)) > 9 Then
If Left(Range("A" & i), 9) <> start And Right(Range("A" & i), 9) <> start Then
Count = Count + 1
End If
End If
End If
End If
i = i + 1
Loop
' MsgBox "Total new SDR found = " & Count
total_count = Count - 1 'this is to make sure when no entries are present we get a total count of 0 (exclude the "END")
End Sub
============================================
Please see below for the code. (I kept pretty much the same code as you gave me). My SDR right now has 6 digits.
Data below in my column A:
+++++++++++++++++++++++++++++++++++
SDR344678, SDR676856
SDR790657
SDR676856
SDR344678
SDR583850
SDR748393
END
+++++++++++++++++++++++++++++++++++++++++++
CODE:
=====================
Private Sub CommandButton1_Click()
Dim start As Range
Dim total_count As Range
Set start = Range("A1")
Set total_count = Range("B1")
Dim i
i = 2
Do Until Range("A" & i) = "END" 'I added a given condition to end the loop, since I will have empty cells
dup = i + 1
If Range("A" & i) <> start Then
If Range("A" & dup) <> Range("A" & i) Then
Count = Count + 1
If Len(Range("A" & i)) > 9 Then
If Left(Range("A" & i), 9) <> start And Right(Range("A" & i), 9) <> start Then
Count = Count + 1
End If
End If
End If
End If
i = i + 1
Loop
' MsgBox "Total new SDR found = " & Count
total_count = Count - 1 'this is to make sure when no entries are present we get a total count of 0 (exclude the "END")
End Sub
============================================
