Comparing Two Excel Worksheets

[twilliamson2]

I am using Excel 2000.

I have two worksheets with payroll data from 1 pay period in January 2005 and the other from 1 pay period February 2005.

I would like to see the employees that was recently added to payroll by comparing the two files. I do have SSNs in the files.

Please Help!

 

[SkipVought]

Hi,

Your payroll data ought to be in a single table, not chopped up into a multitude of sheets or wrokbooks -- makes the data virtually USELESS!

Then you could run a simple Pivot Table Report to see the COUNT OF employee (number of occurrences 1 or 2) for the ranges of interest.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[twilliamson2]

Hi,

I do not want just a simple count of the number of employees. I would like to see a list of new employees that was recently added to payroll.

Please help!

 

[SkipVought]

Tha could be done with a Query from a single table.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[twilliamson2]

Can you elaborate on the solution a bit more? Are you talking about import the two excel worksheets into Access?

 

[SkipVought]

Create a SINGLE table with your source data on a sheet.

Use Data/get External Data/New Database Query - Excel Files -- YOUR WORKBOOK -- YOUR NEW SHEET...

Use MS Query to return employees from date2 not in date1

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[twilliamson2]

I am having difficulty following the instructions. I combined the data from the January worksheet and February worksheet into 1 Access table. Then in excel,

I clicked on Data > get external data > New Database Query >

Then I selected the combined table and selected all fields that I wanted in my query.

Now I am up to the point where the following question is asked "Filter the data to specify what rows to include in your query." I would like to add all rows. How do I get a list of employees that is included in 1 pay period and not the other. Shouldn't I base my filter on SSN? I am lost. Please help!

 

[SkipVought]

What are the Columns in your table.

Please post a few rows of relevant sample data that dempnstrate your problem.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

the SQL would have a where clause something like...

Where Employee Not In (select employee from table where Paydate=Date1)
  And Paydate=Date2

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[twilliamson2]

A few examples of the columns are

SSN
CAMPUS
PAYDATE
TITLE CODE


Examples of Rows of information are:

January 2005 Payroll Data
SSN CAMPUS PAYDATE TITLE CODE
111333333 12 1/5/2005 6578
190870000 13 1/5/2005 8979

February 2005 Payroll Data
SSN CAMPUS PAYDATE TITLE CODE
111333333 12 2/16/2005 6578
190870000 13 2/16/2005 8979
000000001 12 2/16/2005 5555
--

I would like to see only the employee that were not on the January 2005 payroll. From the example above, the following employee should be in my list

SSN CAMPUS PAYDATE TITLE CODE
000000001 12 2/16/2005 5555

This employee was not on the January 2005 payroll, but appeared on the February 2005 Payroll.

 

[SkipVought]

SELECT SSN
FROM `D:\My Documents\MyStuff\dbTest`.`Payroll$` `Payroll$`
where SSN Not In 
(
SELECT SSN
FROM `D:\My Documents\MyStuff\dbTest`.`Payroll$` `Payroll$`
where month(paydate)=1
)
  and month(paydate)=2

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[drrocket5292]

you can just copy the new table next to the previous one and do a countif inside and if formula, for example if your first list of ssn is 200 people long and five columns wide and you copy this months list into last month spreadsheet on say column g so that both lists are next to eachother, then you could put this formula to the right of the second list
=if(countif($A$1:$A$200,$g1)=0,g1,"") and then copy it five columns over and all the way down and you would have everyone who's ssn was not on the old list. then copy and paste it into wordpad and copy and paste it back into a new excel spreadsheet, sort it and you have your list.

 

[SkipVought]

COUNTIF???

What if the employee was there in the PREVIOUS date and NOT in the CURRENT date?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[KenWright]

As Skip said, drop your data tables one under the other so that they look like this

Date SSN CAMPUS PAYDATE TITLE CODE Count
01/01/2005 111333333 12 05/01/2005 6578 1
01/01/2005 190870000 13 05/01/2005 8979 1
01/02/2005 111333333 12 16/02/2005 6578 1
01/02/2005 190870000 13 16/02/2005 8979 1
01/02/2005 000000001 12 16/02/2005 5555 0

Note that I have added a date field of the first of each month for each data set.

Last column (F2) comprises the following formula assuming your data is in A1:E1000

=SUMPRODUCT(--($E$2:$E$1000=E2),--($A$2:$A$1000<>A2))

Copy down, use Data / Filter / Autofilter and filter on Feb in Col A and 0 in Col F.

Alternately do all the above bar the filter and then throw it into a Pivot table and then filter within there.

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------