How to reconcile transactions list to a budget monitoring master sheet?

Bloberto

New Member
Joined
May 31, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
So for work I have a salary budget monitoring sheet that uses sumifs to find 3 criteria’s, an employee no, position number, and a cost centre. Each month I download the salary report that is about 4000 rows long and 20 columns wide and filter these salary transactions into my master monitoring sheet using the sumifs mentioned above. Each month we get new employees and some people move teams (cost centre) and so a new row must be added each time this happens. Is there an easy way for me to find these new employees from the transactions download based on their employee number, cost centre and position number and get a list of these 3 combined columns for those that are not currently in my master sheet?

I’m the past I have been finding these by creating a pivot for each of my master sheet and transactions sheet, adding rows of the cost centres and employee numbers and comparing these pivots row by row to find what is missing, but this is really time consuming.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

What you are describing is really a relational database, and typically, these types of things are much easier to do with a relational database program like Microsoft Access (though you can use Excel's Power Query to do lots of database type operations). In Access, it is easy to create filtered queries, and compare one query to another to identify unmatched records.
 
Upvote 0

Forum statistics

Threads
1,187,025
Messages
5,961,165
Members
438,520
Latest member
annamemma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top