Comparing columns, then copying data to new sheet...

**negativespin** · 11-20-2012, 02:39 PM

Hey folks, here's what I have for you. I've managed to get this done before in a more complicated, time-consuming fashion, but I need to do this every couple of months and am looking for a better way to produce what I need.

Basically, I have a list of employees and accompanying information that I generated/edited months ago, and I need to compare that with a list of employees that I generated from our Domain's active directory today. The goal is to come up with an accurate list of current employees.

Why not just go with the current list, you say? The problem is there are a couple of hundred "User" accounts in AD that aren't actual people, and there's no way for me to filter those out beforehand, unfortunately. Also, I had to go in and manually add certain data (i.e. "Manager") for many users on my last list, which I would like to pull over onto the list I am creating now.

I hope this makes sense. TIA.

**djapigo** · 11-20-2012, 06:48 PM

Hi negativespin,

Can you give us a sample spreadsheet (change info for security) and what results that you want to see... click on Go Advanced for attaching stuff...

Thanks,
Dennis

**negativespin** · 11-21-2012, 01:37 PM

Okay, so here you go...

I can work out the formatting stuff separately, just need to find an easy to compare data and copy over the employees that are still here/that have started since the last report, and not copy those who are no longer with us.

Pardon the random mixing of movie references in my names ;-)

Thanks!

**djapigo** · 11-21-2012, 03:03 PM

Hi negativespin,

Sorry, but I won't be able to help find a nice solution for you. Personally, I would have just used a bunch of MATCH or VLOOKUP functions to do this. But I'm sure there are other ways to do this.

Here are a couple of options that I'm sure someone else can help:
1. Automate it in Excel using VBA macros
2. Pivot Tables (Alt-D-P) with consolidated ranges
3. Access or other databases (union query)

Anyway, good luck and hope you find a nice solution...

Dennis

**negativespin** · 11-21-2012, 03:06 PM

Yeah, thanks. Maybe I'll try messing around with those... unless someone else can come up with a better suggestion? A VBA Macro would be awesome, but it isn't my forte.

**djapigo** · 11-21-2012, 03:38 PM

By the way, are you only going by name to distinguish people between the lists? Or are you using a SSN or ID number? Are you afraid there could be duplicate names?

Just wanted to add my thoughts...