Excel Spreadsheet Comparison

[Demonic Angel]

Ok, I have a report I need to do for my boss by tomorrow - regarding information on two Excel spreadsheets.

What I need to do is compare the information on Spreadsheet A with that on Spreadsheet B and merge the compared information as I need information from both A & B for my report. Its not as easy as copying and pasting, as Spreadsheet B also contains a lot of information I dont need!

I basically need to use Spreadsheet A, which has a list of companies. Find those companies on Spreadsheet B, along with the values given on Spreadsheet B and put those values in Spreadsheet A!

I also need to be able to delete/hide duplicate information, as Spreadsheet B contains a lot of duplicate information and I dont need the duplications.

Can someone tell me if this can be done in Excel, as I dont think it can and if it can't I need to find out a way to do it before tomorrow pm!

I'm not stressed.... honest! :ffs:

And before anyone suggests anything manual.... we're talking 5000 records on Spreadsheet A and 20,000 on Spreadsheet B! ;)

[vmail]

wouldnt it be easier to import the data into an access database then use a SQL query on your data, you could use the DISTINCT keyword to hide duplicate information. (should take 5 mins to 1 hr to do)

If you are good at programming you could use the VBA in excel

have a look at http://www.formulasoft.com/xlsc.html I think they have a free trial

the manual method may be a bit easy, but it depends on the data structure (this is what I do when comparing 16581375 fire/smoke detector addresses)

1) copy and paste the data from one file to the other

2) in a blank column put something like =A1-A2 then the next row =A2-A3 and so on (you can use the drag method to autofil the rest)

3) sort the new column and delete all rows which have the value of 0

[DaveSR]

Wow ..... lots to do lol :P

Can't really help as I don't know enough but here is something simple showing the basics (it's for my accounts lol)

It adds up all the totals on one page and then adds them all up on another - also shows hiding cells etc.

I'm sure you can work it out by looking at the code ...... good luck !

Can't upload it here - emailed it to you

Edited December 12, 2006 by DaveSR

[vmail]

wrong thread

[Gnome]

vlookup formula?

[Demonic Angel]

Thanks for the email Dave!

I've found a programme on the net thats helped stacks with the transfer and duplication.... some work needed on my part but I have all of tonight and tomorrow before my boss needs this report!

Oh the joys of project management! :D

Thanks everyone!

[Boro Sera]

If you had more time I'd suggest writing your own macro to do all of it for you.

It's not as hard as it sounds, although a grounding in VB really pays off !

[Gnome]

Or just turn round to them and say "Do it yourself big poo poo men!"

[Demonic Angel]

Or just turn round to them and say "Do it yourself big poo poo men!"

I do actually want to keep my job though John Boy.....