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
Add a comment
|
3 Answers
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
11 Comments
Arup Rakshit
Hello Horner, Is there any Direct function or way in VBscript which can give how many rows of any specific columns filled with data?
Ekkehard.Horner
@user1878162 - please post a new question.
Arup Rakshit
please help me in my below post - stackoverflow.com/questions/13823912/…
Ekkehard.Horner
@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
@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.
|
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
Comments
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
