Excel file comparison advice needed asap

P

pojut

Gawd
Joined
Dec 28, 2009
Messages
541
Hello all, I have a question pertaining to a request by my boss at work. We're prepping validation documentation for an internal audit, and I have been tasked with making sure three different Excel files from three different database releases match up (i.e. release 18 has everything in it that was in release 17, release 19 has everything in it that was in release 18, etc.)

Can anyone provide me with a macro, a calculation, or a program that I can use to compare different Excel files against each other? Being able to compare a single column across each Excel file would work as well. Please help as soon as you can! This one has me completely baffled -_-;;
 
You need the match function. Make an empty column next to the one you want to verify, then do =match and follow the prompts for what to enter. If you get a # as a result, that's the cell # on the corresponding sheet (though it starts from where you select, so it may be off from the actual field listing on the other sheet). If it says false, then that entry is not on the other sheet.
 
...so you're telling me the answer lies within Excel itself? Of course it does. ::does the "I coulda had a V-8" smack::

Thanks for the help :)
 
Here, I made up an example for you:

excelmatch.png


So my formula is =MATCH(A2,D:D,FALSE)

A2 is the cell I want it to find in the field of D:D (the entire column), and you want your match type to be false.

The field you're looking in can be in another workbook/sheet, just navigate over to it, and highlight where you want to search.

Once you've got the formula in one filed, just mouse over the lower right corner of the field you have the formula in until you see a thick black plus sign. The click and drag it down, and excel will automagically change the formula to A2, A3, A4, etc.. for as far down as you drag it.

Here, I even made you a little video. :)

http://www.newoldie.com/forumpics/excelmatch.avi
 
Last edited:
Oldie said:
Here, I made up an example for you:

excelmatch.png


So my formula is =MATCH(A2,D:D,FALSE)

A2 is the cell I want it to find in the field of D:D (the entire column), and you want your match type to be false.

The field you're looking in can be in another workbook/sheet, just navigate over to it, and highlight where you want to search.

Once you've got the formula in one filed, just mouse over the lower right corner of the field you have the formula in until you see a thick black plus sign. The click and drag it down, and excel will automagically change the formula to A2, A3, A4, etc.. for as far down as you drag it.

Here, I even made you a little video. :)

http://www.newoldie.com/forumpics/excelmatch.avi
Click to expand...


So all I gotta do is make a new excel file, copy the whole "requirement #" column from each traceability matrix document, plug in the function, auto-filter to show only the N/A, and I'm good to go.

DUDE! Thank you SO much! These Excel files have literally thousands of requirements listed in them from the past few years...this will make comparing them SUPER SUPER easy.

I FREAKIN' LOVE THE [H]
 
LOL...here's one word of caution for you though, if you're not searching an entire column, you need to add some $ signs into your lookup array, before and after the first column indicator, or it won't copy correctly. Here's another video showing what I'm talking about:

http://www.newoldie.com/forumpics/excelmatch2.avi

If you aren't aware that can happen, and happen to not be looking through an entire column, it will drive you batshit crazy. :p
 
If your organization is excel heavy, and it sounds like yours is, learning things like match and vlookup can make you a friggin hero around there. If you ever see people visually sorting data, it's your queue that you know you can do it in a few seconds instead of how ever long it's taking them to do it. Excel has some very awesome data comparison functions built right in.
 
Cool, thanks for the heads up :)

Seriously though, thank you. This will make a massive difference, now and in the future (this is my first audit I've dealt with. Oooofa, I hope they don't happen too often)
 
Oldie said:
If your organization is excel heavy, and it sounds like yours is, learning things like match and vlookup can make you a friggin hero around there. If you ever see people visually sorting data, it's your queue that you know you can do it in a few seconds instead of how ever long it's taking them to do it. Excel has some very awesome data comparison functions built right in.
Click to expand...

Sadly, it's a cost-saving thing.

"Well, we already have Excel on all your computers, why should we spend a thousand dollars or whatever on licenses for software specifically tailored to do this? Excel works, right?"

Oy -_-;;
 
Yeah, excel gets abused like a red-headed step kid in some places. :p

My primary customer has a multi-million dollar SAP setup, and all of the people I work with do everything in excel, and then have some poor IT person dump the files into SAP later instead of using it live like they're supposed to. It's really hard to break bad habits when someone gets used to a tool.

I would also learn how to do vlookups if I were you. It's a quick easy tool that's probably the #1 time saver if you have multiple sheets of data.

Say I have a sheet of employee ID #'s, and it lists their name, department, who they report to etc...and someone wants you to add everyones extension to that sheet. If you have another spreadsheet that has their employee ID#'s and their extensions on it, you can use vlookup to automatically search for the ID# on the other sheet, and write in their extension on the one they want it added to. Pretty snazzy.

When you can pull off feats of magic in applications that save your company countless hours of manual work, you become invaluable, which is a nice thing to be now days. Just try to not teach anyone else if you can help it. :D
 
ooo...that would indeed be quite handy. They're not-so-subtely pushing me to handle validation documentation for all new database updates/releases, so something like that would certainly come in handy. Time to find some online tutorials!
 
You must log in or register to reply here.
Back
Top