Excel - Loop Through a Column and Compare?

[jaymdouglas]

I have to create a macro and I'm stuck on this step.

I have to take the first Employee Number in Column A and first see if there are any other entries in column A with the same Employee Number.
Perform a task based on that.
Then go to the next employee number, and the next, etc.

Anyone know how to perform this task?

[DKenny]

hopefully this will help.

Sub Duplicates()
Dim rngCompareArea As Range
Dim rngCheckCell As Range
Dim sCellText As String
Dim lOccurance As Long
 
    'First establish the range that contains
    'the cells to be checked
    
    'This is just an example, you weill need to reference the correct range
    Set rngCompareArea = ThisWorkbook.Worksheets(1).Columns(1)
    
    'Loop through each cell in the range
    For Each rngCheckCell In rngCompareArea.Cells
        
        'Get the value in the cell
        sCellText = rngCheckCell.Text
        
        'Ignore blanks
        If sCellText <> "" Then
            
            'How many times does that value
            'occur in the range?
            lOccurance = Application.WorksheetFunction.CountIf(rngCompareArea, sCellText)
            
            'If its more than once....
            If lOccurance > 1 Then
                
                '...Perform your action here
                
            End If
        End If
    Next rngCheckCell
    
    'Clear object variables
    Set rngCompareArea = Nothing
End Sub

[jaymdouglas]

Good news, your code works. It runs through and counts the number of re-occuring numbers.

The issue now, is if the same number appears in Column A I need it to perform a check on it's matching column and then perform some math.

So if Employee Number 53 is found twice then add their number in another column up.

[DKenny]

Have you considered the SUMIF function within excel? It sounds like this is what you need.

[jaymdouglas]

Perhaps a visual refrence may help me a little.

I have a spreadsheet with the following data. I need to go through it line by line and first find all matching "Pers.no." and then add their corresponsing "Days" column.

And then throw all this data on another sheet with it all grouped together. There is one more IF stmt to be included but that is for later on!

Pers.no.    Name                    Org Key             Type            Days
53          K#thl##n W#lk#nsh#w TWKS - CENT SU      (No absence)    3
63          R#ch#rd W#dd#####n  CITY COUNCIL        (No absence)    7
68          L#wr#nc# T####          CITY COUNCIL        (No absence)    12

[DKenny]

you don't need code to do this, SUMIF will give you exactly what you described here.


Here's an example, have a look at the formulas in cells I2:I4

[jaymdouglas]

I understand what your saying, unfortunately this has to be done in a MACRO because there are some other calculations and actions to be performed after it has calculated the days.

[DKenny]

OK
Can you explain what "other calculation and actions" need to be performed?

[jaymdouglas]

Long story short, I have to check the "TYPE" category, if the word "Authorized Unpaid Absences" appears in any of that persons rows then I have to subtract 3 from the total of the "Days" column.

Then if the Days total exceeds "3" I have to delete them from the list, otherwise keep it.

I know its confusing.

[Dnereb]

If there's no counter argument it's best to let Excel Sort your range
for the specific column first.
This way all double occurence's are follow each other up.
and your data will be easier to manage.