How to get repeated value from excel using VBScript

In order to get repeated value from excel using VBScript, we can utilize dictionary object in VBScript. We have a ready-to-use function here 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.

This code leverages a feature of dictionary object i.e. exists(). 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 incremented and stored as the item for the key. Once the for loop finishes running, you will have a dictionary with all the unique values in the key and the occurrences as the item for corresponding keys.

This is one of the easiest way to get repeated value from excel using Dictionary in VBScript.

Complete VBScript code to get 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.