Comparing Tables

**jomili** · 02-09-2010, 02:01 PM

I need a macro to compare two 3-column tables having the same information in similar formats. I've attached a spreadsheet of the two tables. They originally come from two other spreadsheets, and i could run the routine either from those other two spreadsheets or on the sheet I've attached.

The first run of this is going to be the hardest, as I'm trying to sync up data from two different reporting mechanisms, and we've been working to get the naming conventions right, but I'm sure there are errors I'll have to clean up. But after this, there will be a weekly comparison of the Number of FTEs, and so a weekly opportunity to be able to boil out any differences, which I would like to paste to another sheet in the same or a different workbook.

I appreciate any help you can provide.

Thanks,
John

**Richard Buttrey** · 02-09-2010, 02:08 PM

Hi,

That's a very minimalist workbook. You're not Swedish are you

Regards

**jomili** · 02-09-2010, 02:19 PM

hmm, that's odd. The first time I tried to post it it didn't post, the second time it posted as blank data. Let's see if third time's the charm.

**Richard Buttrey** · 02-09-2010, 03:09 PM

Hi,

The file came through OK but it's not clear (to me at least) what the task is. As you say the two tables contain similar information, and I guess you're trying to identify differences and matches between the two. If so can you give a few examples of what you expect to see as a result of the macro?

Regards

**jomili** · 02-09-2010, 03:24 PM

Sure. Sorry for not being clear enough.

We have two tables from two sources. Ideally the Job Title names will match, but for right now they probably don't all match, so that's the first thing I need to look for, names within PACs that are on one table and aren't on the other. After we get running there should be few of those, but we'll still have to check.

Once the names match up the way it should work is this: within PAC X, for Job title "Bottlewasher", one table has a 3, the other has a 3. Everything matches, ignore. For the next Job Title "WindowCleaner", one has a 3, the other has a 4. That's a problem. Paste the PAC, Job title, and what each report shows as the correct quantity so someone can look into it later.

For any job titles that are on one table but aren't on the other, paste the PAC, Job title, and what each report shows as the correct quantity so someone can look into it later.

Sound easy enough? It's easier for me to describe than do. I hope you can help.

Thanks.

**jomili** · 02-18-2010, 12:24 PM

Bump, no response.

I have a new example, and here's a review of the situation:

We have two tables from two sources in two different worksheets. Working from one of those two worksheets I would like to pull out the differences between the two. I have a routine that can put both tables in the same spreadsheet, one under the other (as in the attached example) or we could leave the tables where they are if that would work better.

The tables each have a PAC column, a Job Title (or Position Function title), and a total column. I want to compare the Job Titles and Totals within each PAC and copy the ones that are in one table but not the other to another spreadsheet.

It should work like this:
Case 1: PAC X, Job title "Bottlewasher", one table has a 3, the other has a 3. Everything matches, ignore.

Case 2: PAC Q,Job Title "WindowCleaner", one has a 3, the other has a 4.
That's a problem. Paste the PAC, Job title, and what each report shows as the correct quantity so someone can look into it later.

Case 3: PAC M, Job Title "Floorlicker", one table has this job title, the other doesn't. That's a problem. Paste the PAC, Job title, and quantity so someone can look into it later.

I'd sure appreciate some help on this.