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.

  • Copy the above program as is
  • paste it into a text file
  • Change the values of variables ExcelPath, sSheetName, nRowStart, nCol to reflect your current requirements
  • save it as a .vbs file
  • Double-click on the file icon to run the VBS program
  • If everything is working as expected then the program will display two messageboxes.
  • first message box will get the count of most repeated value from excel
  • second message box will get most repeated value from excel and display it.

You may also read

Discover more from Automation Script

Subscribe to get the latest posts sent to your email.

Related Posts