Compare two columns.. Return missing data and extra.

jaejay

New Member
Joined
Jun 7, 2017
Messages
7
Hello

Basically at the moment I am managing our emplpees all on excel (trying to move from this).

For bow the team is using... Alot of look ups and such and I'm just trying to clean up my workbook.

Two sheets:
One - all employees present and gone

Two - another sheet which will populate the register for the day.

What I want is a macro... Or a way that's isn't too much for the workbook to compare the names between the active employees in main sheet and employees for the day on register.

Both sheets have name, payroll number and active column. Active is determined by start and end dates.

I have a simple countif to see which numbers are active for that day. And there is a discrepancy of 6,there are 6 more in the register than should be. To save me going through 600 names.... Is there an easier way to find what's missing?

Would like it to work both ways. I just can't seem to wrap my head around this.... Other than doing an index match on each name... Or a countif. But that's 600 formulas I'd rather avoid.

Anyway... Any help will be appreciated.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What do your formulae look like now? You really should be able to do this without writing 600 separate formulae.

Seeing what you're starting with we can likely make the changes needed to get it all in one formula.
 

jaejay

New Member
Joined
Jun 7, 2017
Messages
7
What do your formulae look like now? You really should be able to do this without writing 600 separate formulae.

Seeing what you're starting with we can likely make the changes needed to get it all in one formula.
I'm building something new so I don't have any formulae. Mainly because I wanted to avoid 600 forumale!

However this is what the others in team are doing:

They have a main staff control sheet. Daily register names are taken from the Main Employee Sheet.

To get their shift hours they do this: =SUMIFS('RESOURCE LOG'!$D:$D,'RESOURCE LOG'!$C:$C,'DAILY REGISTERS'!$B17,'RESOURCE LOG'!$B:$B,'DAILY REGISTERS'!$D17)
To get their site they do this: =IF(AND('STAFF CONTROL SHEET'!$AF$16<=$B17,'STAFF CONTROL SHEET'!$AF$16>0),'STAFF CONTROL SHEET'!$AE$16,'STAFF CONTROL SHEET'!$Y$16)

Their excel sheet is about... 8mg for one month.

I managed to create one that will last 8 months and its less than 700kb (using keys to populate shifts from an array helped :))

I've attached what I'm doing as a very simple audit log. Very simple countif of active users. I just need to be able to get the missing ones out so I can update as required...

Hope that makes sense.
 

Attachments

  • MrExcel.PNG
    MrExcel.PNG
    50 KB · Views: 11

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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