• 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.

compare two different sheets if value >500, copy the details to sheet 3

shanan

New Member
Hi Experts,

I need your assistance to solve this.. I'm trying to compare two different sheets( Sheet 1 and Sheet 2), and if the data in sheet 1 difference is more than 500, I want the macro to check why is it different against Sheet 1 data and copy paste the details to sheet 3

Conditions that to include is:( check the attached excel workbook)

1 - if Shanan "Net pay " in sheet 2 more than 500 in Shanan's "Net pay" in sheet 1, check why is different is it because of allowance or overtime ( to compare against sheet 1's allowance and overtime details) and employee name and reason for difference in sheet 3

2. If shanan have more than 1 line of net pay in sheet 1 or sheet 2, plus all values before start the comparisons.

Thanks guys, Have a great day ahead
 

Attachments

  • test.xlsx
    9.2 KB · Views: 3
Hi:

I have put a basic macro in place could not test it extensively, as the data points given by you is very less. Try testing it with a bigger data set and let me know how did it go

Note: I would recommend you to use the employee Id instead of name as it will be unique.

Thansk
 

Attachments

  • test.xlsm
    19.8 KB · Views: 6
Hi Nebu,

Great Job, It works

I'm attaching another excel sheet (with macro), can we collaborate below details in our Macro?

1) 63 columns to be compare if the difference is more than 500 and prompt the results in sheet 3
2) if you look at my sheet 3 ( which i created deliberately), can we prompt the results as such?

Thanks alot for your time! appreciate it!
 

Attachments

  • Book2.xlsm
    38 KB · Views: 17
Hi:

Can you specify from which column to which column the comparison has to be made.

Thanks
 
Hi:

I have modified the macro.

Notes:
  • The columns of both sheet 1 and sheet 2 have to be same.
  • I am comparing the sheet with less rows to the sheet with more rows.
  • You can ignore the numbers and the color coding, I used it just to have a visual for me to understand the data, it is no where used in the macro.

Play around with the macro and let me know if the output make sense.

Thanks
 

Attachments

  • Book2.xlsm
    39.7 KB · Views: 3
Hi,

It works well. Thanks. After discussion with my team mate, we wanted to add more requirements into this excel to add more value

1) Column E- to populate Sheet 1 amount based on the reasons
2) Column F - to populate Sheet 2 amount based on the reasons
3) Column G - Compare Column E & F, If E>F, input message as "Pervious" and if F>E, input message as "Current"

*Auto add borders for all active columns
*Auto add title for each column as below:
Column A: Personnel Number
Column B: Full Name
Column C:prev. Personnel No.
Column D: Reasons >500
Column E: Previous Month Amount
Column F: Current Month Amount
Column G: Remarks

* Comparisons should be from Column AE- CQ only.

Thanks alot!
 
Hi:

On a second thought I completed it , it was fairly easy.

Thanks
 

Attachments

  • Book2.xlsm
    41.5 KB · Views: 4
Hi,

Awesome work! It works exactly like how I wanted it to be.

I was just going through the data, noticed that there is a problem with the columns itself..

For example,
1) Column AV in sheet 1 not same as Sheet 2 ( AV in sheet 2 is "2815 Study Leave - Amt" which is not anywer in sheet 1)
2) Column BE in Sheet 1 not same as sheet 2 ( BE in sheet 1 is "3090 Relocation Allowance - Amt" which not anywer in sheet 2)
3) Column BH in sheet 1 not same as sheet 2 ( BH in sheet 2 is "3150 Salary Continuance - Amt" which not anywer in sheet 1)
3) Column BI in sheet 1 not same as sheet 2 ( Bi in sheet 1 is "3205 Project Bonus to Super - Amt" which not anywer in sheet 1)
4) Column BT in sheet 1 not same as sheet 2 ( BT in sheet 2 is "5752 Excluded LB ETP 16.5% - Amt" which not anywer in sheet 1)
5) Column BW in sheet 1 not same as sheet 2 ( BW in sheet 2 is "7075 Purchased Leave payback - Amt" which not anywer in sheet 1)
6) Column CA in sheet 1 not same as sheet 2 ( CA in sheet 2 is "7084 Motor Vehicle Adj Pre Tax - Amt" which not anywer in sheet 1)
7) Column CD in sheet 1 not same as sheet 2 ( CD in sheet 2 is "7402 Credit Card Deductions - Amt" which not anywer in sheet 1)
8) Column CH in sheet 1 not same as sheet 2 ( CH in sheet 1 is "8211 Super - COF EE Pre tax - Amt" which not anywer in sheet 2)

Can we use the nurmeric id that we have infront of each category for comparisons..? like for ex:

If any of the column of sheet 2 not in sheet 1, that reason>500 should populate as per the column's title.

*Auto add borders for all active columns
*Auto add title for each column as below:
Column A: Personnel Number
Column B: Full Name
Column C: Prev. Personnel No.
Column D: Reasons >500
Column E: Previous Month Amount
Column F: Current Month Amount
Column G: Remarks

Thanks alot
 
We need to compare if "/560 Net Pay - Amt" in sheet 2 is more than 500 againts sheet 1's /560 Net Pay - Amt. if yes, compare columns based on the numeric id's and populate the results accordingly.

Forgot to mention this.
 
Hi:

Find the attached. You really do not need to auto populate the headers and format it using macro, you have to do it once manually. I have fixed the other issues.

Thanks
 

Attachments

  • Book2 (1).xlsm
    39.7 KB · Views: 10
It works well.. Thank you so much for your help.


In another topic. Just want to ask. what will be the code to copy data from sheet 1 from one excel workbook to another workbook which currently open?
 
Good day Nebu, Hope you had a great weekend.

Thanks alot.. It works well at my end.

Just lack of one thing..
1) If Sheet 1 "1101 Period Salary - Amt" is higher than Sheet 2 "1101 Period Salary - Amt"
2) than check if there is any leave taken in sheet 2 or not that caused of the less pay in sheet 2.( check leave type at the bottom of this message)
3) if no leave taken but still lesser "1101 Period Salary - Amt" in sheet 2
4) than populate the reason >500 as "Check LWOP"
5) if leave taken and that's why the Sheet 2 "1101 Period Salary - Amt" is lesser, than populate the reason as "Salary lesser due to paid leave"

Also,
Can we have this this workbook data to be generated at another workbook using the same macro?

Once all reasons populated at Book 2's sheet 4,continue with below macro

Conditions:
1. Open WTR report
2. Look for the employee name which in Sheet 4 at book 2
2. If Employee name is Shanan and Reason more than 500 is any of from the list below, Sort WTR report column "Wage Type Long Text" as "Amount Paid".
3. Input remarks at Column T
4. Generate the reason from book 2 to wtr report.


I have attached sample of WTR report with deliberately created remarks for your review.

Reasons:
1101
LWOP
2110/2A10
Overtime
2000/2005
Casual EE
1101
Missing Timecard
3105
Bonus
4886/4887
Travel allowance
2400/2401
Claim
4250
Annual leave encashment
2099/2199
Uplift allowance
Income tax/ /420
Tax
8100/8201/8205
Superannuation
3260
Statutory PPL
3030
Call Allowance
7402
Credit Card deduction
7063/7081/7082/7083/7084
Lease Plan
3020/3021/3023
LAFHA allowance
7066/7067
Salary Sacrifice
3150
Salary continuance
1101
New hire
1101
Increase salary
5001/5002/5031/5041/5042
Termination
7091/7092
Pay Plan
7215
Medibank
7801
Child support
7071/7075
Purchase leave

Leave type:
2501 Annual Leave - Amt
2510 Purchased Leave - Amt
2601 Personal Leave - Amt
2701 Long Service Leave - Amt
2801 Compassionate Leave - Amt
2810 Jury Duty Leave - Amt
2815 Study Leave - Amt
2820 Maternity Leave Paid - Amt
 

Attachments

  • WTR Report.xlsx
    19.2 KB · Views: 1
If Sheet 2 contains value in column AP- AW, that means they have taken leave and that has caused the reductions.. if no value in AP- AW, and Period Salary is lesser than Sheet 1, then they have to "Check LWOP".
 
Hi:
Can you please check and confirm whether the following conditions are satisftying in the a
1) If Sheet 1 "1101 Period Salary - Amt" is higher than Sheet 2 "1101 Period Salary - Amt"
2) than check if there is any leave taken in sheet 2 or not that caused of the less pay in sheet 2.( check leave type at the bottom of this message)
3) if no leave taken but still lesser "1101 Period Salary - Amt" in sheet 2
4) than populate the reason >500 as "Check LWOP"
5) if leave taken and that's why the Sheet 2 "1101 Period Salary - Amt" is lesser, than populate the reason as "Salary lesser due to paid leave"

Note:

You will have to do extensive testing using all possible combinations.

Thanks
 

Attachments

  • Book2 (1).xlsm
    43 KB · Views: 8
Hi,


It works. but only for "Jimmy".. for others it doesn't populate the reasons as "Check LWOP".

Can you help me to add "Check LWOP" as new reason and plus all the amounts of leave and state at "Previous Month Amount" or "Current Month Amount"? (do not replace with leave, as I tested Jimmy's "Annual Leave" reason changed to Check LWOP)

Just noticed, the Macro should total up the value in sheet 1 or sheet 2 when ever necessary before do the comparisons.. for example, this workbook sheet 1 contains 3 different details for same employee (Jimmy), while at sheet 2 has only 1 jimmy.. hence macro should total up before do the comparisons..

* net pay in sheet 1 for all three jimmy should total up and same goes to other columns in sheet 1
*this situation can also happen at Sheet 2, depends on the report that we get. So It need to do the same total up function before comparisons.
*if we able to do this, then it sheet 4, there will be only 1 jimmy with all the details and same goes to other employees

Thanks Man
 
Hi:

Check LWOP as a new reason column easy to add. The totaling part I need sometime to think of a logic how to do it.Now its becoming a little complicated.

The reason why it worked only for Jimmy is because only Jimmy had a salary which is higher in Sheet1 than in Sheet 2 , hence I asked you to test it extensively changing numbers to cater all the scenarios that may arise.

Thanks
 
Hi,

Sure, I can wait. It will be really helpful if you can help me with the totaling part.

Haha, I got it why it works only for Jimmy now. never notice that. Thanks Thanks
 
Hi:

You need to explain me how you want to total the values for various employees with the same name. How should your final output look like, do yo need all the 63 columns totaled as per their column headings and compared? In that case there will be only unique employee names in the output. Let me know.

Thanks
 
Good day,

1) I guess the unique code would be the Personnel number at column A at each sheet as it differs employee to employee.
2) Exactly, all 63 columns should totaled as per their heading..
For example "Annual leave" for jimmy in sheet 1 has 3 different value, all of the should total up and compare against in sheet 2. and if in sheet 2 there are 2 different value for annual leave, it should be totaled as well before comparison.

Thanks.
 
Hi:

Please find the attached.

I have made pivots out of the raw data to get the totals and ran the macro out of these pivots , the macro code and the logic is the same.Could you test it with all possible scenarios and let me know.

Note: I have converted the raw data into table this way it will be easier to update the pivots.

Thanks
 

Attachments

  • Book2 (1).xlsm
    71.1 KB · Views: 4
Back
Top