How to get most repeated value from excel using VBScript
In order to get most repeated value from excel using VBScript, we have a ready-to-use function created in VBScript. All you need to do is parse the path of the excel file, the name of the sheet, the index for the row from where the value has to be read, and the column index.
How this function works is, at first a dictionary object is created, and using a for loop every value in the column is stored in the dictionary object as a key. Now if a particular value already exists then the count is noted as the item.
Complete VBScript code to get most repeated value from excel.
ExcelPath= "C:\Users\NS\Desktop\test.xls"
sSheetName= "Sheet1"
nRowStart=1
nCol=3
'get most repeated value from 3rd column starting from row no. 1
getMostRepeatedValueFromExcel ExcelPath,sSheetName,nRowStart,nCol
'ExcelPath= Path of the excel file
'sSheetName= Sheet name
'nRowStart= Row number to start find data
'nCol= Column index to find data
Function getMostRepeatedValueFromExcel(ExcelPath,sSheetName,nRowStart,nCol)
Dim objexcel, objWorkbook, objDriverSheet, columncount, rowcount
set objexcel = Createobject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open(ExcelPath)
Set objDriverSheet = objWorkbook.Worksheets(sSheetName)
'columncount = objDriverSheet.usedrange.columns.count
rowcount = objDriverSheet.UsedRange.SpecialCells(11).Row
Set dictValues = CreateObject("Scripting.Dictionary")
Dim MostKey
intHighest = -1
for i=nRowStart to rowcount
sKey= objDriversheet.cells(i,nCol)
if dictValues.Exists(sKey) Then
dictValues.Item(sKey) = cStr(cInt(dictValues.Item(sKey)) + 1)
else
dictValues.Add sKey, "1"
end if
if cInt(dictValues.Item(sKey)) > intHighest Then
intHighest = CInt(dictValues.Item(sKey))
MostKey = sKey
end if
Next
objWorkbook.Save
objWorkbook.Close
objExcel.Quit
Set objExcel = Nothing
msgbox intHighest
msgbox MostKey
End Function
How to run this vbscript program
If you are not aware of “How to run the above program”, you can follow the below steps.