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.