Tough One To Crack for excel specialists

[Shiva]

Hello All
I have a set of records with five columns viz.,
Name Address City State Date Status

All the columns are self-explanatory. The Status column has one of
values (approximate, confirmed, estimates).


The program has to find duplicate records based on the condition that
Name Address City and State are same.


Once, the duplicate records are found, the program has to check for
Status of the record. If the status is confirmed, then the program
should cut the other duplicate records and paste them in another sheet.



If none of the records have Status as confirmed, then the next criteria

is date. The program has to check for the latest date and cut and paste

other records in another sheet.


If more than one record has status as confirmed, then apply the same
above rule.


I know its complex.


Thanks in advance


Shiva

 

[Susan]

i'm having a go of this on my own......
i have some questions, tho.

first, find duplicate record of row 1. ok.
say row 5 is also a duplicate. then check
to see if it is confirmed - row 1, row 5, or
both? if the status is estimate or approximate
both duplicate rows (1 & 5) stay put???

say rows 1 and 5 both say confirmed. row
1 has a date of 10/31/06 and row 5 has a
date of 12/15/06. you want row 5 cut
& pasted to the same sheet where the
duplicates get pasted, or another sheet?

how many sheets should there be in total?
should ANY duplicates remain on the 1st
sheet? after all the above happens for row 1,
then i'm assuming you want to go to row 2 &
check that, correct? and you'd also need
the blank row left behind by row 5 to be
removed, right?

yes, it is complicated but it is do-able, if
i can understand what you need.
thanks for explaining.
susan

 

[NickHK]

I would have thought an SQL approach would be easiest.
Find dupes and deleting/inserting is standard stuff.
Use ADO on the closed workbook.

NickHK