Advanced compare of two worksheets

[skoalnreds]

Hi -

Using Excel 2007: I'd like to do this with VBA as doing it with
VLOOKUP( ) would be quite tedious and slow:

I have two worksheets ("May" and "June") with identical layouts,
consisting of 575 columns of data (all formatted as text.) The first
column on both sheets is a unique identifier. What I would like to do
is this:

1. For all rows on "June" that are not on "May" (based on the unique
identifier), I want those entire rows on "June" copied to a new tab in
the workbook called "New".

2. For all unique identifiers on "June" that do exist on "May":
compare the values in each column (all 574 remaining columns after the
unique identifier). If ALL column values are exactly the same, copy
that entire row to a new tab called "No Changes". Otherwise, on a
new tab called "Changes", put the unique identifier in Column A, the
name of each column with an unequal value (from Row 1 of "June") in
Column B, the "May" value in Column C and the "June" value in column
D. Each change should appear in a separate row.

Any ideas on how to accomplish this? Thank you very much...

 

[Jim Cone]

There are commercial alternatives.
--
Jim Cone
Portland, Oregon USA
( http://tinyurl.com/XLCompanion )





"skoalnreds" <[email protected]>
wrote in message Hi -
Using Excel 2007: I'd like to do this with VBA as doing it with
VLOOKUP( ) would be quite tedious and slow:
I have two worksheets ("May" and "June") with identical layouts,
consisting of 575 columns of data (all formatted as text.) The first
column on both sheets is a unique identifier. What I would like to do is this:

1. For all rows on "June" that are not on "May" (based on the unique
identifier), I want those entire rows on "June" copied to a new tab in
the workbook called "New".
2. For all unique identifiers on "June" that do exist on "May":
compare the values in each column (all 574 remaining columns after the
unique identifier). If ALL column values are exactly the same, copy
that entire row to a new tab called "No Changes". Otherwise, on a
new tab called "Changes", put the unique identifier in Column A, the
name of each column with an unequal value (from Row 1 of "June") in
Column B, the "May" value in Column C and the "June" value in column
D. Each change should appear in a separate row.
Any ideas on how to accomplish this? Thank you very much...

 

[skoalnreds]

There are commercial alternatives.
--
Jim Cone
Portland, Oregon  USA
(http://tinyurl.com/XLCompanion)

"skoalnreds" <[email protected]>
wrote in messageHi -
Using Excel 2007:  I'd like to do this with VBA as doing it with
VLOOKUP( ) would be quite tedious and slow:
I have two worksheets ("May" and "June") with identical layouts,
consisting of 575 columns of data (all formatted as text.)  The first
column on both sheets is a unique identifier.  What I would like to do is this:

1.  For all rows on "June" that are not on "May" (based on the unique
identifier), I want those entire rows on "June" copied to a new tab in
the workbook called "New".
2.  For all unique identifiers on "June" that do exist on "May":
compare the values in each column (all 574 remaining columns after the
unique identifier).  If ALL column values are exactly the same, copy
that entire row to a new tab called "No Changes".    Otherwise, on a
new tab called "Changes", put the unique identifier in Column A, the
name of each column with an unequal value (from Row 1 of "June") in
Column B, the "May" value in Column C and the "June" value in column
D.  Each change should appear in a separate row.
Any ideas on how to accomplish this?  Thank you very much...

Wow, I never thought of that. Thanks!