How do you go through each employee number and see if they have 2 codes to their ID?

[jojo52479]

I was given a project to go through each employee ID going back to the first employee and see when they switched from union to nonunion. Below is an example of my spreadsheet. Using VBA, what code would I be able to use to go through each EE# and determine when they switch to union codes 99, 87, and 88. Untimely, I want to only keep the EE# that went to those union codes and delete the EE that dont have these union codes. Any assistance could help. I have over 300K of lines so going one EE at a time wouldnt work.

EE#	Hire Date	Union Code
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	58C
25	3/11/1988	58C
25	3/11/1988	58C
25	3/11/1988	8
25	3/11/1988	8
25	3/11/1988	8
25	3/11/1988	8

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

What is a union code compared to a non-union code?

 

[jojo52479]

We have numerous Union codes. The only non union codes we have are those three. So the 3 you see on the screen are union codes but we have 100's. I just need to determine which ones are in those 3 and delete the rest.

 

[Fluff]

Your post is confusing & potentially contradictory.
But if i understand correctly, you want to delete all rows that do not have 8, 28C or 58C in col C, is that right?

 

[jojo52479]

My apologies if this is confusing. What im looking for is a code that will go through each EE# column A and see if they were moved into a non union code 99,87, and 88. For example: the below Employee was in a union and moved to a non union base on code 89. I would want to keep that one. The next employee only stood in union so I would want to delete that employee. We have over 40K employees so I need a code that would go through each employee in column A and see if they were moved in column C.

90984 10/15/2009 8CR
90984 10/15/2009 8CR
90984 10/15/2009 8CR
90984 10/15/2009 8CR
90984 10/15/2009 8CR
90984 10/15/2009 8CR
90984 10/15/2009 89
90984 10/15/2009 89
90984 10/15/2009 89
90984 10/15/2009 89
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR
90981 10/11/2009 8CR

 

[Fluff]

So far you have said

when they switch to union codes 99, 87, and 88

moved into a non union code 99,87, and 88

Added to which you have said

The only non union codes we have are those three.

But you latest example shows a code of 89

Also do you want to keep the entire employee record if they moved to non union, or just the non union rows?

 

[jojo52479]

My apologies. Non union codes are 99,87,88. I was just giving an example. Yes, I want to keep all the information of the EE if possible. There are a total of 24 columns in this report but my main focus is the employee number and union codes. But upper management needs all the rest of the information. Thank you so much for your assistance.

 

[Fluff]

try

Sub KeepNonUnion()
   Dim Cl As Range
   Dim Flg As Boolean
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Select Case Cl.Offset(, 2).Value
            Case 87, 88, 99: Flg = True
            Case Else: Flg = False
         End Select
         If Not .exists(Cl.Value) Then
            If Flg Then .Add Cl.Value, Nothing Else .Add Cl.Value, CStr(Cl.Value)
         ElseIf Not IsEmpty(.Item(Cl.Value)) Then
            If Flg Then .Item(Cl.Value) = Empty
         End If
      Next Cl
      Range("A1").autofilter 1, .items, xlFilterValues
      ActiveSheet.autofilter.Range.Offset(1).EntireRow.Delete
      Range("A1").autofilter
   End With
End Sub

 

[jojo52479]

Hi Fluff,

Thank you for your help. When I run the code I get this error: Run time error 5 : Invalid procedure call or argument.

 

[Fluff]

Which line of code gives that error?