Compare two columns.. Return missing data and extra.

[jaejay]

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.

 

[JonXL]

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]

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.