Comparing rows of data in two worksheets

[Sheena]

Hi,
I need to compare data in two worksheets.

Column A is the unique identifier.
There are 9 columns and approximately 4,000 rows on each sheet.

What I need to be able to do is compare all 9 cells of each row on
sheet 2, with the data in sheet 1.

I then need to highlight which cells have changed on sheet 2, or if
it's a "new" record.
(Basically it's data as at the beginning of the month (sheet 1),
compared to the end of the month (sheet 2)).

At the moment my attempts at writing macros for this have not been
successful.
Any help would be great!
Thanks,
Sheena.

 

[Bernie Deitrick]

Sheena,

I will send you an add-in that I wrote to do just this: it creates a third
sheet that show what is different between the two sheets, and highlights the
changes and similarities on the originals.

HTH,
Bernie
MS Excel MVP

 

[AxeldraX]

can i have this add-in please. I have the same problem

Axe

 

[Frank Kabel]

Hi Axel
you may have a look at

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla (http://www.cpearson.com/Zips/Compare.ZIP)

 

[Bernie Deitrick]

Frank and Axel,

The compare utility downloadable from Chip's site does a row by row
comparison, expecting the rows to be mostly indentical, with no insertions,
deletions, or re-ordering of rows. Mine does a database-style comparison
based on key numbers in one column, so the rows don't need to be in the same
order, and works with row additions and deletion. (I haven't finished the
final re-write to accept column deletions, insertions, and relocations, but
that's not a big deal, and my wife didn't need that feature for her
work....)

I will send you a copy, Axel.

HTH,
Bernie
MS Excel MVP

Hi Axel
you may have a look at

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla (http://www.cpearson.com/Zips/Compare.ZIP)

 

[Bernie Deitrick]

Axel,

I will send you a copy, at what I hope is a valid email address.

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi Bernie
could you put me on your recipient list for this macro

 

[Bernie Deitrick]

Frank,

As long as you promise to send me your improved version.... ;-)

HTH,
Bernie
MS Excel MVP

 

[Max]

Hi Bernie,

Hope you're still on this thread ..

Would be delighted to receive a copy of your add-in.
My email: xdemechanik <at>yahoo<dot>com

Thanks
Max
xl 97

 

[Bernie Deitrick]

Max,

Hope you're still on this thread ..

Yes I am, though you'll need to wait until Monday morning...

Bernie
MS Excel MVP

 

[Max]

Thanks !

 

[Volker Hormuth]

Hi Bernie,

may I also have this code ?

Thanks!
Volker

 

[Guest]

Berni

If possible I would like a copy of your macro also (as I would like to modify it to compare text) - see my post in this foru

Thanks in advance for your hel

Mar

(e-mail address removed)

 

[Guest]

Hi Bernie

Would greatly appreciate a copy of your code

Cheer
Crai

----- Bernie Deitrick wrote: ----

Axel

I will send you a copy, at what I hope is a valid email address

HTH
Berni
MS Excel MV

 

[Guest]

Hi Bernie

Would greatly appreciate if you could forward your code to me as well

Many thank
Crai

----- Bernie Deitrick wrote: ----

Axel

I will send you a copy, at what I hope is a valid email address

HTH
Berni
MS Excel MV

 

[wuming]

Hi,
i need vba codes to compare 2 worksheets and display the results in
third worksheet. Anyone can help??

btw Bernie if u r still here pls send me a copy of yr macro too thank
in advanced!
(e-mail address removed)

 

[Lee Jeffery]

Bernie,

If you are still checking this thread ... I have just done a search t
try to find info to compare monthly reports in order to identify an
new data which appears in the most recent report, and I found thi
thread.

Would you be happy to send me a copy of your add-in if you think thi
will do what I need, please?

My email address is (e-mail address removed)

If you think your add-in is unsuitable for my purposes, do you have an
suggestions as to where to start developing my own code to do this?

Many thanks.

Lee.
Raw beginner
Hobart, Tasmania

 

[Oreg]

Bernie,


If it's not too much trouble and you're still checking this thread
could you please forward me a copy of your macro?

My email is (e-mail address removed)

Thanks,

Ore

 

[Chris_Fifield]

*Hey Bernie,

I'm having the same problem as Sheena. I need to compare two simila
sheets. I need a macro that will help me compare values that ar
present in both sheets and values in one that's missing in the other
and vice versa. I was told that there is some nested function that
need to create, but I don't know where to start.
Any help is greatly appreciated!

(e-mail address removed)
Jacob *

I would copy the data from both spreadsheets into separat
2-dimensional arrays, then loop through one array to find matches i
the other array. Use the array indexes themselves to know where on th
spreadsheet the matches are found