Match names&dates [Closed]

Report
-
 derby -
Hello,
i have a master list of names &dates in col A & B and a similar list in cols C & B which is a longer list as below.

The Trader 09/08/2008 The Trader 09/08/2008
The Trader 30/08/2008 The Trader 19/08/2008
The Twelve 06/08/2008 The Trader 30/08/2008
The Twelve 23/08/2008 The Twelve 06/08/2008
The Doctor 14/08/2008 The Twelve 23/08/2008
Thewhirl 30/08/2008 The Doctor 14/08/2008
This Eddy 06/08/2008 Thewhirl 30/08/2008
This Eddy 12/08/2008 Thewhirl 17/08/2008
Tidy 05/08/2008 Thief 15/08/2008
Tidy 13/08/2008 This Eddy 02/08/2008
This Eddy 06/08/2008
This Eddy 12/08/2008
This Eddy 13/08/2008
Tidy 05/08/2008
Tidy 13/08/2008
Tidy 15/08/2008
Truism 22/08/2008
What i want to do is sort the lists so that the names & dates in cols C & D match the names and dates in cols
A & B ,and the unwanted names and dates in cols C&D are deleted,the lists are over 2000 rows long ,if i sort numerically they dont match as there are more names & dates in col C&D.I am using excel 2007 and am a
macro novice.
Thanks for your help

1 reply

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Just write this formula in E2 and drag it down ... .then using filter you can selected "delete" from the column E and delete the row ....

E2=IF(AND(A2=C2,B2=D2),"","Delete")

If you have any further query, do let me know ...

Many thanks for your reply but this formula deletes data which is required to match to master cols A&B
The cols should end up as below.The lists can be over 2000 rows long,so perhaps a macro which will go down master cols A&B and insert blank cells when a match is found in cols C&D,the unwanted cells in cols C&D
for example The Trader 19/08/2008 can then be deleted or coloured,sorted and then deleted.
Sorry my first explanation was not very good first time,hope you can help



The Trader 09/08/2008 The Trader 09/08/2008
The Trader 19/08/2008
The Trader 30/08/2008 The Trader 30/08/2008
The Twelve 06/08/2008 The Twelve 06/08/2008
The Twelve 23/08/2008 The Twelve 23/08/2008
The Doctor 14/08/2008 The Doctor 14/08/2008
Thewhirl 30/08/2008 Thewhirl 30/08/2008
Thewhirl 17/08/2008
Thief 15/08/2008
This Eddy 02/08/2008
This Eddy 06/08/2008 This Eddy 06/08/2008
This Eddy 12/08/2008 This Eddy 12/08/2008
This Eddy 13/08/2008
Tidy 05/08/2008 Tidy 05/08/2008
Tidy 13/08/2008 Tidy 13/08/2008
Tidy 15/08/2008
Truism 22/08/2008