I am using Excel 2007 on Windows 7 Home Premium 64bit.
I have searched the forum, but have not been able to find an
example that does exactly what I am trying to do.
My Workbook contains 2 Worksheets, both with the same headings in row 1.
Sheet2 is initially empty, except for the headings in row 1.
I would be grateful to learn how to create a Macro as described below.
The following is a sample of the data in Sheet1:
A B C D E F
1 First Last Address City State Zip
2 John Smith 123 Main St New York NY 10021
3 Mary Smith 123 Main St New York NY 10021
4 John Smith 123 Main St New York NY 10021
5 John Smith New York NY 10021
6 Joe Smith 123 Main St New York NY 10021
7 Jack Smith 123 Main St New York NY 10021
8 John Smith 123 Main St New York NY 10021
9 Mary Smith 123 Main St New York NY 10021
10 Bill Smith New York NY 10021
11 John Smith New York NY 10021
12 John Smith 124 Main St New York NY 10021
I want to create a Macro to operate as follows:
In the above sample data, rows 2, 4, and 8 are identical,
so I want to copy rows 4 and 8 to Sheet2, and delete them from Sheet1.
Similarly:
rows 3 and 9 are identical, so row 9 should move to Sheet2;
rows 5 and 11 are identical, so row 11 should move to Sheet2.
Rows 6, 7, 10, and 12 are all unique, so should be unaffected by the Macro.
New records will be added to Sheet1 periodically, so the Macro will be used
each time to move any newly discovered duplicates to Sheet2.
The Macro should make no assumption about how the rows are ordered,
and should not change their order.
I have uploaded two Workbooks, such that Workbook1 shows the
original data, and Workbook2 shows the result I want to obtain.
Bookmarks