CODEWITHSUNDEEP
vbaexcel
Lodewijk
Lodewijk

Reputation: 100

Excel-vba compare, condition, delete

I have an Excel file with activities for different colleagues, like so:

Start date || end date || usage % || colleague

Now what I would like is to see what people are working on now / when they are available for what % / since when they have been available.

So deleting rows which end date is in the past, unless that is the only row for a colleague, because then I want to see that row as a measure of " since when has he been available for a new job ".

But then colleague B for example, has several things going. How would I go about removing old stuff but keeping the two lines that he;s working on now + the line that he will be working on as of 1 dec?

They can have a usage % of >!00 thats fine.

Sorry for the long description... Im not looking for code, just the psuedo code / logic to tackle this!

Upvotes: 1

Views: 58

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Im not looking for code, just the psuedo code / logic

It seems to me that you just need the following:

  1. A simple loop to go through each row and look at the end date.
  2. If the end date is in the past, then go to step 3.
  3. If the employee has only one record, do not delete, otherwise go to step 4.
  4. If end date is in the past, and there is more than one record - delete.

How would I go about removing old stuff but keeping the two lines that he;s working on now + the line that he will be working on as of 1 dec?

I believe simply testing if the end date is in the past will suffice for each scenario?

In pseudo code that might look like:

For i = [num_rows]
    If end_date < today Then
        If [COUNTIF(colleague_column, colleague)] > 1 Then
            rows(i).delete
        End If
    End If
Next

or

For i = [num_rows]
    If end_date < today And [COUNTIF(colleague_column, colleague)] > 1Then
            rows(i).delete
    End If
Next

or in actual VBA (we would loop backwards in the case of deleting rows) assuming your data is in columns A:D with no headers:

For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If CDate(Cells(i, 2)) < Date And WorksheetFunction.CountIf(Range("D:D"), Cells(i, 4).Value) > 1 Then
        Rows(i).EntireRow.Delete Shift:=xlUp
    End If
Next

Upvotes: 1

Related Questions