Algorithm For Compare Rows by Three Columns

[iwanna]

Hello to all,
I have a situation regarding comparing data from several columns. To make this more specific, I've attached an excel file for a better understanding.
- I want to compare all the information from the range A6:D127 to S7:W91 (the information is available on sheet 2) and to emphasize the difference (non matching paycodes, or hours)
- if, for example, the records match (name of the employees from range A6:D127 matche to those from S7:W91 and the same thing for the rest of the columns) I have to insert all the information in the report from sheet 1
- I also have to insert in the report from sheet 1 the discrepancy between the columns.
- as you see in the sheet 2, the the two tables have a differentiate number of rows. So, this means that I have some employees which don't show up in the other table. I must report this also.
The report is done practically manually but I hope there is a faster solution.

I hope I make myself cleared about this. I really need a solution for my problem (a function, or a macro code or something else) and I would really appreciate it .

Thank you very much!

[SamT]

Hello, iwanna, and welcome to VBA Express.

Thank you for providing a pretty good example of what you need to accomplish. If you do not understand VBA good enough to modify any solution we offer, then you should also provide us with the real world structure of your workbook(s). By that, I mean the real names of the book(s), and sheets the tables are located in.

I understand that you recieve two reports and must compare the two and show any discrepancies.

It is possible that adding Sheets and Tables to your workbook(s) might make it possible to write much better code. Can you do this?

Some names appear in Sheet2 column A but not in Sheet2 Column S, and yet they do not appear as a discrepancy in Sheet1. How do we know when to NOT list a discrepancy?

Some Pay codes are in Sheet2 Column C, but not in Sheet2 column U. What are we doing with those?

[iwanna]

Hello,

The data from sheet 2 are extracted from two reports, I must compare those on sheet 2 and the differences are shown on sheet 1. The thing that I overlooked is, not only the differences must be shown on Sheet 1, also the correct records . I can add of course other sheets or buttons or tables to the sheet 2. I don't must change the layout of the report on sheet 1. The discrepancies could be referring to paycodes or hours.

The report is entitled "paid vs billed' that's why on the left side of the sheet 2 we have what is paid and on the right what is billed. And on the sheet 1 is the same thing.. on the left are data from the table 1 from sheet 2 and on the right side are records from the table 2 from sheet 2 ( the reason of having two section of paycodes and hours). If we find an employee which was only paid and not billed, we must fill in the report until the section GPM inv. From that point (Gpm inv) must be filled with records from table 2 from sheet 2( if there are available). If the data is missing we leave it empty and put a note. And of course, it works in the same way for the records which appear only on billed side( we leave the paid section unfilled)

I attached the list with the employees that must not be included in this report (sheet 1).

Regarding independent contractors, these records must be put in a different sheet (creating a table for them with all the data related).
Attachment 10700don't include.xlsx

I hope this infos will help you. Sorry for my spelling or grammar errors. I would appreciate very much if you find a solution to my problem.
Thank you!

[SamT]

@ iwanna,

Look at this attachment and tell me if the Blue cells are correct.

I deleted many Rows of data to make the file size smaller.

Can you force Bonilla White, Belinda to use one name only? If you cannot, then can we put her in the Report as two persons (names)?

Also, will you look at and approve the Business Rules below

@ Code Experts

Business Rules:

• Raw Reports contains Paid table and Billed Table.
• Billed table includes Staff and Independent Contractors
• Paid Table includes IC's, but not Staff
• Paid vs Billed Report shall not contain Staff or IC's
• IC Report shall contain ICs only

Possible Algorithm:

Objects and Variables:

• UDT for Required Fields
• Dictionaries Paid, Billed, Billed Contractors and PaidContractors: Keys = LastName & FirstName & PayCode; Items = UDT
• Dictionaries ICs and Staff, Key = LastName; Item = FirstName

Compare Paid Table to ICs Dict, If Exist then add to PaidContractors, Else add to Paid Dict.
Compare Billed Table to Staff Dict. If not Exists Then compare to ICs Dict, etc as before

For each Key in Billed 
Write UDT fields to Report Record
If Paid.Exists(Billed.Key) then 
Write Paid(Billed.Key) Fields to Record
Else: Write Comments to Record.
End If
Next Key

Repeat with Billed and PaidContractors for their report.

[iwanna]

Hello again,

I took a look to your attachment and I've made the corrections and added some notes.

Regarding business Rule:
1=> is oky
2=> Billed raw report doesn't include Staff or Independent Contractors
3=> Paid raw report includes Staff and Independent Contractors
4=> is oky
5=> IC Report shall contain ICs only - is oky

I cannot change Bonilla White name but you can use two codes for her or I will insert her manually.

Thanks again for your effort!