8

Suppose an Excel sheet has a column named Student Names and the column has duplicate values. Say,

Student
=======

Arup
John
Mike
John
Lisa
Arup

Using VBScript, how can I get unique values as below?

Arup
John
Lisa
Mike
Helen
  • 87,344
  • 17
  • 243
  • 314
Arup Rakshit
  • 116,827
  • 30
  • 260
  • 317

3 Answers3

13

The VBScript tool for getting unique items is a dictionary: add all the items as keys to a dictionary and dictionary.Keys() will return an array of the - per definitionem - unique keys. In code:

  Dim aStudents : aStudents = Array("Arup", "John", "Mike", "John", "Lisa", "Arup")
  WScript.Echo Join(aStudents)
  Dim aUniqStudents : aUniqStudents = uniqFE(aStudents)
  WScript.Echo Join(aUniqStudents)

' returns an array of the unique items in for-each-able collection fex
Function uniqFE(fex)
  Dim dicTemp : Set dicTemp = CreateObject("Scripting.Dictionary")
  Dim xItem
  For Each xItem In fex
      dicTemp(xItem) = 0
  Next
  uniqFE = dicTemp.Keys()
End Function

output:

Arup John Mike John Lisa Arup
Arup John Mike Lisa
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • Hello Horner, Is there any Direct function or way in VBscript which can give how many rows of any specific columns filled with data? – Arup Rakshit Dec 06 '12 at 13:09
  • 1
    @user1878162 - please post a new question. – Ekkehard.Horner Dec 06 '12 at 13:48
  • please help me in my below post - http://stackoverflow.com/questions/13823912/to-move-the-cell-values-in-a-group-from-right-to-left-if-any-group-of-cells-are – Arup Rakshit Dec 12 '12 at 15:14
  • Does Dic.Keys() will automatically remove the duplicates? So Can you tell me what is the difference between Dic.Keys and Dic.Keys()? – Arup Rakshit Dec 21 '12 at 08:14
  • 1
    @VBSlover: please study the Docs for Dictionary to learn (1) you can't put duplicate keys in a dictionary, so there is no removing at all; (2) the .Keys method is a method that returns something (an array of the dictionary's keys), the statement "x = d.Keys()" calls this method as a Function and assigns the return value, so according to the rule "use param list () when calling a function" the () should be there, *but* Mr. Gates' own sample code omits the () and still works, *so* decide for yourself whether you want to be pedantic (like me) or not. – Ekkehard.Horner Dec 21 '12 at 08:46
  • Yes. I 100% agree with you.But now my question is `For Each xItem In fex dicTemp(xItem) = 0 Next` this part how removes the duplicates.Just a guess does it remove the duplicate keys at their entry time? correct me please If i am wrong. – Arup Rakshit Dec 21 '12 at 10:08
  • 1
    @VBSlover: Straight from the Docs: "If key is not found when changing an item, a new key is created with the specified newitem." To spell the implication out: If the key exists, newitem is just assigned. – Ekkehard.Horner Dec 21 '12 at 10:14
  • Yes,I understood that,if a Key say 9 is already added with an item, and a second attempt with the same Key (here 9) will not allow to add one more 9,rather it will update the `item` which 9 was previously holding. Nice concept. I am now 100% sure in this concept. Thanks @Ekkehard :-) – Arup Rakshit Dec 21 '12 at 10:27
  • @ParthAkbari If http://stackoverflow.com/a/6592801/603855 doesn't help, post a new question (with some more details). – Ekkehard.Horner Dec 17 '14 at 06:54
0

Alternatively, since your source data is in an Excel spreadsheet, you could use SQL to fill an ADODB Recordset:

Option Explicit

Dim filePath, sheetName
filePath = "C:\path\to\excel\workbook.xlsx"
sheetName = "Sheet1"

Dim connectionString
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filePath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=No"""

Dim sql
sql = "SELECT DISTINCT Student FROM [" & sheetName & "$]"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, connectionString

Do Until rs.EOF
    WScript.Echo rs("StudentName")
Loop
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
0

If you don't want a Dictionary you can use the following to compare each element in the array to itself.

Info = Array("Arup","John","Mike","John","Lisa","Arup")

x = 0
z = ubound(Info)
Do
x = x + 1
Do
z = z - 1
If x = z Then
Info(x) = Info(z)
ElseIf Info(x) = Info(z) Then
Info(x) = ""
End If
Loop Until z=0
z = ubound(Info)
Loop Until x = ubound(Info)
For each x in Info 
If x <> "" Then
Unique = Unique & Chr(13) & x
End If
Next

MsgBox Unique
Moir
  • 379
  • 4
  • 14