• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

identifying the second payment

Hi
I know how to solve the following by performing 2 pivots table, identifying duplicates,
filtering, copy to a new tab..... I managed to what I will explain...took me a log time, someone okay with excel. I tried explaining how to do this... but it did not work, the person did not understand. so I thought maybe a macro will do the job... hence my post.

Here is the requirement.
I have a list of employees and I need to identify who received a salary raise. In this excel, it is termed as 'Exempt Salary' and also 'Non Exempt Salary' (column B).

Basically, column E and F show the start and end date of the salary period, so if a person has the following (2 rows in excel), it basically means that the the person received a new salary.

[once again - only applicable if a person has two rows]. If a person only has 1 row - Please ignore]


In the example, for employee 111, the new salary of 25,000 is valid from the date of 04-Nov 16. The old salary of 20,000 was for the period of until 13-Nov-2016


Here is the magic VBA I need.

I need to identify all such occasions (for an employee that has 2 rows for salary changes) and to able to state summaries the changes on one row showing the the following the old and new data and then the differences.

I have added an example tab - hopefully that will make everything clear.

Thank you for anyone who can solve this dilema how to automate for 1000s of lines.

I will attached a sample excel file, showing how the output should look like.

Thank you once again anyone who try this challenge, and save the numerous manual steps needed for and automated with a macro.
 

Attachments

  • vb - salary change.xlsx
    17.9 KB · Views: 9
Hi @david gabra

This should work:
Code:
Sub test()

    Dim c As Range
   
    For Each c In Sheets("raw data").Range("C2:C" & Sheets("raw data").Columns("C").Cells(Rows.Count).End(xlUp).Row).Cells
        Dim lrow As Integer
        lrow = Sheets("Test").Columns("A").Cells(Rows.Count).End(xlUp).Row + 1

        If c.Value = "Exempt Salary" And c.Offset(-1, -2).Value = c.Offset(0, -2).Value Then
            Range("A" & c.Row - 1 & ":G" & c.Row - 1).Copy Sheets("Test").Range("A" & lrow)
            Range("D" & c.Row & ",F" & c.Row & ",G" & c.Row).Copy Sheets("Test").Range("I" & lrow)
           
            Sheets("Test").Range("M" & lrow).FormulaLocal = "=K" & lrow & "-G" & lrow
            Sheets("Test").Range("N" & lrow).FormulaLocal = "=M" & lrow & "/G" & lrow
        End If
    Next c
       
End Sub

Please see attached
 

Attachments

  • vb - salary change.xlsm
    30.5 KB · Views: 8
incredible...... once button instead of 3 pivots...

I now see that there is two points
It should show should both Non Exempt Salary and Exempt Salary
Currently it only shows Exempt Salary
Can you help fix please.

also when it copies the second row, it only copies that start date - can we please make it so that is also copies the end-date too from that row. I forgot to add on the initial design spec.

thanks
 
Last edited:
@david gabra

Here you go (please see attached)
Code:
Sub test()

    Dim c As Range
    Dim arr(1) As String
   
    arr(0) = "Exempt Salary"
    arr(1) = "Non Exempt Salary"
   
    For Each c In Sheets("raw data").Range("C2:C" & Sheets("raw data").Columns("C").Cells(Rows.Count).End(xlUp).Row).Cells
        Dim lrow As Integer
        lrow = Sheets("Test").Columns("A").Cells(Rows.Count).End(xlUp).Row + 1

        If Not IsError(Application.Match(c.Value, arr, False)) And c.Offset(-1, -2).Value = c.Offset(0, -2).Value Then
            Range("A" & c.Row - 1 & ":G" & c.Row - 1).Copy Sheets("Test").Range("A" & lrow)
            Range("D" & c.Row & ":G" & c.Row).Copy Sheets("Test").Range("I" & lrow)
           
            Sheets("Test").Range("N" & lrow).FormulaLocal = "=L" & lrow & "-G" & lrow
            Sheets("Test").Range("O" & lrow).FormulaLocal = "=N" & lrow & "/G" & lrow
        End If
    Next c
       
End Sub
 

Attachments

  • vb - salary change (1).xlsm
    31.9 KB · Views: 14
Hi
We have a really minor bug that I can't explain.
Whenever we put the real data, we get an additional column, which we remove manually, but we would like to know if there is a way of amending so it will not show.

We get 'Relocation Loan' but your script only states

arr(0) = "Exempt Salary"
arr(1) = "Non Exempt Salary"

I do not understand - can you help.

Please see attached file - I added a file with the real data - changed for anonymity - but left in the type so you can see that Relocation Loan also appears.

Thank you
 

Attachments

  • Why Relocation.xlsm
    27.1 KB · Views: 16
Hi,

Well, that is not a bug... the criteria was:
if a person has the following (2 rows in excel)
and for exempt and non exempt salary...

In this new example that criteria is met in the "Relocation Loan" cases so it is working correctly.

The code looks for exempt and non exempt salary and checks if previous line is the same employee no.
It appears that the logic needs to be adjusted to make sure only the correct info is moved over to the output sheet.

Can you explain what should be copied in this new scenario? In what cases is having two rows and exempt or non exempt salary to be ignored?

Thanks
 
Hi

I would like to only show cases for

arr(0) = "Exempt Salary"
arr(1) = "Non Exempt Salary"

Even if there are duplicates for other, I do not want to show.

Thank you

David.
 
Hi David

I'm still having some doubts as to what should and should not be copied... I understand that you only want to show Exempt and non Exempt salary, but what do you copy over to new sheet in the employee no. 1 and 3 cases?
In the first example you said that if second line was exempt or non exempt salary, the macro should copy data from the line above to a new sheet (A:G) and data from exempt and non exempt line to (I:L).
In this case, for no. 1 and 3 what should be pasted in (A:G)?
Can you please upload a sample file with the desired output?

Thanks
 
Hi David

You understand correctly that I only want to show Exempt and non Exempt salary, so I only want to copy Exempt and non Exempt salary that appear more than once. Even if another element appear more than once, I do not want to copy.

In the first example I said that if second line was exempt or non exempt salary, the macro should copy data from the line above to a new sheet (A:G) and data from exempt and non exempt line to (I:L).
- you are correct -

In this case, for no. 1 and 3 what should be pasted in (A:G)? - Nothing should be copied since these DO NOT refer exempt and non Exempt salary

Does that help

David.
 
Hi David

You understand correctly that I only want to show Exempt and non Exempt salary, so I only want to copy Exempt and non Exempt salary that appear more than once. Even if another element appear more than once, I do not want to copy.

In the first example I said that if second line was exempt or non exempt salary, the macro should copy data from the line above to a new sheet (A:G) and data from exempt and non exempt line to (I:L).
- you are correct -

In this case, for no. 1 and 3 what should be pasted in (A:G)? - Nothing should be copied since these DO NOT refer exempt and non Exempt salary

Does that help

David.
Hi,

Sorry for the late response but I was away on vacation :(

In that case you can use:
Code:
Sub test()

    Dim c As Range
    Dim arr(1) As String
   
    arr(0) = "Exempt Salary"
    arr(1) = "Non Exempt Salary"
   
    For Each c In Sheets("raw data").Range("C2:C" & Sheets("raw data").Columns("C").Cells(Rows.Count).End(xlUp).Row).Cells
        Dim lrow As Integer
        lrow = Sheets("Test").Columns("A").Cells(Rows.Count).End(xlUp).Row + 1

        If Not IsError(Application.Match(c.Offset(-1, 0).Value, arr, False)) And Not IsError(Application.Match(c.Value, arr, False)) And c.Offset(-1, -2).Value = c.Offset(0, -2).Value Then
            Range("A" & c.Row - 1 & ":G" & c.Row - 1).Copy Sheets("Test").Range("A" & lrow)
            Range("D" & c.Row & ":G" & c.Row).Copy Sheets("Test").Range("I" & lrow)
           
            Sheets("Test").Range("N" & lrow).FormulaLocal = "=L" & lrow & "-G" & lrow
            Sheets("Test").Range("O" & lrow).FormulaLocal = "=N" & lrow & "/G" & lrow
        End If
    Next c
       
End Sub

Hope this works
 
Back
Top