0

My database has 100 columns, with column A being a unique list of employee ID and column K being each employee's pay status (exempt, hourly, non-exempt, etc.). How to Extract a list of employee ID (from column A) that has "exempt" status (from column K) -- using dictionary please! In other words, how to proceed with Step 2 in my code below?

Public Sub ExtractEmployeePayStatus()
      Dim i As Integer
      Dim p As Integer
      Dim cell As Range

      Dim rangeData1 As Range
      Dim rangeData2 As Range

      Dim rangePerform1 As Range
      Dim rangePerform2 As Range

      Dim dictionaryData1 As Scripting.Dictionary
      Dim dictionaryData2 As Scripting.Dictionary

      Dim strValue1 As String
      Dim strValue2 As String

      Dim w As Variant
      Dim v As Variant


      '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<   Step 1 = Load employee numbers (unique list in column A)
      Set rangeData1 = Sheets("Database").Range("A9:A2008")
      Set dictionaryData1 = New Scripting.Dictionary
      dictionaryData1.CompareMode = TextCompare

      With rangeData1
            For Each cell In rangeData1.Columns(1).Cells
                  i = i + 1
                  strValue1 = cell.Text
                  If Not dictionaryData1.Exists(strValue1) Then
                        dictionaryData1.Add strValue1, .Rows(i)
                  Else
                        Set rangePerform1 = Union(.Rows(i), dictionaryData1(strValue1))
                        dictionaryData1.Remove strValue1
                        dictionaryData1.Add strValue1, rangePerform1
                  End If
            Next cell
      End With

      p = -1
      For Each w In dictionaryData1.Keys
         p = p + 1
         Debug.Print dictionaryData1.Keys(p)  '<<<<Possible to print related column K values???
      Next w

      '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<   Step 2 = Load employment status (not unique list in column K)
            '''''How to load this????  How to do Step 2???


      '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<   Step 3 = Compare Step 1 result vs Step 2 result
            ''''' I want to use dictionary per above to extract employee IDs (from column A) whose status is "Exempt" (from column K).
            ''''' I want to use dictionary because I have 10,000 employee IDs.  Dictionary will go faster.

End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
SofiaEd
  • 11
  • 1
  • You mention a database, but this is Excel, right? – LegendofPedro Oct 26 '19 at 18:49
  • Ideally - the data inside the sheet "database" should be stored in an actual database, an easy database to get up and running easily is SQLite. Once the data is inside a database you can use SQL to easily query for the data you need. https://stackoverflow.com/questions/42509154/accessing-a-sqlite-database-in-vba-in-excel/42512968 – dswdsyd Oct 27 '19 at 00:12
  • Step 2 would look like step 1, but before adding the employee Id you would check that ColK for that row is "exempt" BTW if ColA really is unique, your step1 seems overly-complicated. – Tim Williams Oct 27 '19 at 02:41
  • Hi Tim - Yes, I can do exactly as such as get a second dictionary? Or, add it to the same dictionary? And then how do I call the value (debug.print for example ID#57 and get "Non-Exempt"...) – SofiaEd Oct 27 '19 at 04:45
  • Pedro and Syd - Yes, I will try to learn putting the data into a database. Using Access of SQLLite -- which one is better for 4,000 columns and 100,000 rows? – SofiaEd Oct 27 '19 at 04:47
  • IMO the best and fast too will be using normal sheet's filters (Ctrl+Sh+L). You turn on filters, seting "K" column = "Exempt", and You have filtered rows (Columns("A:A").SpecialCells(xlCellTypeVisible)) collection for further calculation. All You can do from VBA code, of course. – barneyos Oct 27 '19 at 08:01

0 Answers0