Search : in
By :

Count unique entry in a cell & column-vb scr

Last answer on Feb 23, 2009 3:52:14 am GMT hossain.iqbal, on Feb 22, 2009 3:20:14 am GMT 
 Report this message to moderators

Hello,
i have been looking a way to count unique entries using VB script. There are a lot of sample VB script but I have a little twist with my situation. Please see below example where consider col A to be one column and each new lines as new rows

Col A

SDR45322
SDR74564
SDR58493, SDR45322
SDR34534
SDR74564

What I expect my answer to be is total new SDR found = 4. My life would have been very simple if I didnt have to deal with multiple SDR in one single cell.

I signed up on this forum with the hope that SOMEONE will be kind / knowledgeable enough to help me out!

Thanks in advance for whoever had a chance to read my question.

Riz

Configuration: MS office 2007
Mac OS X
Safari 525.27.1

Best answers for « count unique entry in a cell & column vb scr » in :
Excel- Count unique entry in different groups Show
Text String count within a Cell Show Text String count within a Cell Issue Solution Note Issue I have number of records (see below) and want to know if I can use a function to count the number of records that contain a specific text string within each cell entry? e.g....
Excel conditional formatting row color Show
[Excel] – Countif Formula Show[Excel] – Countif Formula Issue Solution Note Issue I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3" I want to count in one cell with "Pending...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
Download Internet Cell Boost Show
Download USA Unlisted Cell Phone Numbers ShowHas you friend flown for America without telling you where he/she would settle? Don’t panic! USA Unlisted Cell Phone Numbers is a program which can help you. By using this program, you simply need to know his/her phone number in order to find...
Download Count Days Show
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Spreadsheets - Data Entry ShowCell Content A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics: a type, which means the intrinsic type of the data. There are generally three types of values: numeric values, for example...
Ask your question Reply

1

WutUp WutUp, on Feb 23, 2009 1:01:32 am GMT

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

   
Reply to WutUp WutUp

3

hossain.iqbal, on Feb 23, 2009 2:39:36 am GMT

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!!!

   
Reply to hossain.iqbal

4

 hossain.iqbal, on Feb 23, 2009 3:52:14 am GMT
  • +1

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
============================================

   
Reply to hossain.iqbal

2

Riz, on Feb 23, 2009 2:27:12 am GMT

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!

   
Reply to Riz
Ask your question Reply
They need your help