Staffing Gains/Losses

[jrobin9151]

Excel 2003 running on windows xp. I am trying to create a report that will return the gains and losses in staffing. Each pay period a roster from HR is given listing the names and work locations of the individuals. I am posting each data set into a seperate tab in the workbook. On the main or recap sheet I use the COUNTA function to give me the total number of employees for the pay period, sum it up against the previous pay period and I get the number added or lost. No problem.... Here's where it becomes difficult. I want a formula or process in Excel that will look at the previous pay period, compare it to the current pay period, and return the name of the person that was added or left. I'm assuming I can use a combination formula to do the trick but cannot for the life of me figure out which to use. I've tried the Index,Match formula no luck, I thought may the IF formula but not sure if that'll give me what I need. Any suggestions out there?

 

[SkipVought]

Hi,

You can use MATCH() to compare any two lists to see which ones are not present. If MATCH() exact match returns #N/A!, then that name is not in the lookup range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

Skip! So glad to see your still assisting folks. I've read and used many of your suggestions/solutions very successfully!!!

We have half the fix to this issue. The match returns #N/A But then I need to add the name of the employee that was added or lost from the previous pay period. I've tried compounding the formula to something like this and using the following assumptions. D4 is payperiod(pp) 4 listing of employees, D3 would be pp3 and d2 is pp2.

=IF(MATCH(D4,C4:C10,0)=#N/A,MATCH(D4,B4:B10,0),MATCH(D4,C4:C10,0))

What I'm trying to get the formula to do is compare this pp to the last, if it can't find it then check the pp previous to the one just checked. My assumption is if they're a new employee they would not be listed in the previous pay periods and I list this persons name as a gain. If they're a retirement etc. then they would be in the previous pay period and I would match that name as a loss. However this is getting me no-where... I'm thinking I have a lot of spaghetti logic going on.

 

[SkipVought]

Applying the formula to the previous list, identifies those who are not present in the current list and therefore were REMOVED.

Applying the formula to the current list, identifies those who were not present in the previous list and therefore were ADDED.

What else do you need?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Match will work if the data is sorted. If it isn't, you should therefore sort it. If for some reason you can't (or don't want to), you could also use an array formula.

For example, if last month's employees are in range A1:A30 and this month's are in B1:B30 , then the formula:
{=SUM(IF(A1=$B$1:$B$30,1))=0} will return TRUE if the employee in A1 has left. If you put this in C1 and copy it down it will give you the status of all the employees in column A.

Similarly, the formula:
{=SUM(IF(B1=$A$1:$A$30,1))=0} will return TRUE if the employee in B1 is new.

Note - both of the above need to entered as ARRAY formulae (i.e.Ctrl Shift Enter, not just Enter.

Tony

 

[SkipVought]

Match will work if the data is sorted.

This is ONLY TRUE if you are using the LESS THAN or GREATER THAN Match Type.

The EXACT match type works on ANY list to locate the FIRST occurrence, wherever it appears.

In this case, it works.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Skip's right as usual. I stand corrected.

Tony

 

[jrobin9151]

Skip, thanks. I need the names of the folks that were deleted and added.

I think I have the formula correct for getting the persons name when they're added. But I don't have it right when they're deleted from the list.

Attached is a sample of my spreadsheet

 

[SkipVought]

Many of us have company restrictions that make downloading data impossible.

Please post ALL your examples drectly here.

In the CURRENT list...
[tt]
=if(isna(match(a2,PreviousRange,0)),"",INDEX(PreviousRange,match(a2,PreviousRange,0),1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oops, switched them...
[tt]
=if(isna(match(a2,PreviousRange,0)),INDEX(PreviousRange,match(a2,PreviousRange,0),1),"")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]