Compare daily staffing files and extract or flag differences

[j7hompson]

Hi,


I've read through the recent posts regarding list comparisons but don't know that they work for this situation.


Basically, I receive an excel file via email on a daily basis which contains SAP information for ~1,500 employees. I would like to be able to compare "today's" list with "yesterday's" list each day and extract any difference between the two.


This list contains a number of columns which contain information on position, department, address, etc,...


Differences could include the following;

-employees being added to the list (not necessarily inserted at the bottom)

-employees being removed from the list

-a change in any of the columns for an employee (ex. change in position, change in department)


Here is a shortened example of what I would like to be able to accomplish (there are many more columns than the ones shown below):


File Received 2/7/2012:

Employee#, EmployeeName, Position, CostCenter

2222, John Doe, Manager, 88888

1111, Jane Smith, Director, 55555

3333, Mike Smith, Specialist, 88888


File Received 2/8/2012:

Employee#, EmployeeName, Position, CostCenter

2222, John Doe, Manager, 88888

4444, Sally Doe, Specialist, 88888

1111, Jane Smith, Vice President, 44444

3333, Mike Smith, Specialist, 88888


In this example a new employee (Sally Doe) was added and, Jane Smith's position and costcenter changed


A separate spreadsheet might then display the ongoing differences between each new staffing file or, maybe the differences could be flagged on each new file so that they are easy to identify.


I'm not sure whether this is possible using excel but would appreciate any advice! Let me know if there's anything further I can provide that would help.


Thanks,


Josh

 

[SirJB7]

Hi, j7thompson!


There are several ways to perform this check, using VBA code (more flexible but harder), using combinations of functions in complex formulas (smarter), and the one I'll write, quick-and-dirty but effective.


Let's assume your previous data is in sheet Previous in columns A:D:

[pre]

Employee#	EmployeeName	Position	CostCenter	Just for checking
2222	John Doe	Manager	88888	2222John DoeManager88888
1111	Jane Smith	Director	55555	1111Jane SmithDirector55555
3333	Mike Smith	Specialist	88888	3333Mike SmithSpecialist88888
In E2 type (and copy down):

=CONCATENAR(TEXTO(A2;"General");B2;C2;TEXTO(D2;"General")) -----> in english: =CONCATENATE(TEXT(A2,"General"),B2,C2,TEXT(D2,"General"))


And that your actual data is in sheet Actual in same columns:

Employee#	EmployeeName	Position	CostCenter	Just for checking	Result
2222	John Doe	Manager	88888	2222John DoeManager88888
4444	Sally Doe	Specialist	88888	4444Sally DoeSpecialist88888	Check
1111	Jane Smith	Vice President	44444	1111Jane SmithVice President44444	Check
3333	Mike Smith	Specialist	88888	3333Mike SmithSpecialist88888

[/pre]
Do the same for column E.

In F2 type (and copy down):

=SI(ESERROR(BUSCARV(E2;Previous!E:E;1;FALSO));"Check";"") -----> in english: =IF(ISERROR(VLOOKUP(E2,Previous!E:E,1,FALSE));"Check";"")


In column F you'll have to check rows with "Check" value.

More complex formulas or VBA code may tell you if it's a new added record, what field (column) has changed, and if it's a deleted record (do the same processing on Previous for getting erased records)...

Just adapt it to your requirements.


Regards!