Conditional Filtering with Huge Data

[smartbuilder2k]

Hi All,

I have got 4 columns, namely - A) DefectNo B) Fixed Date C) Action_timestamp D)Action_name.

-> The "DefectNo." column may contain duplicate values. For ex: "Defect100" has arised several times but with different Fixed Date,Action_timestamp & Action_Name.

->The "Fixed Date" column contains date on which a particaular Defect no. has been Fixed in Format "2005-11-01 18:07:40".

->[\b]The “Action_timestamp” column contains dates on which a particular “Defectno” has been send back.

->The "Action_name" column contains action taken for defects. It may contain values namely Fixed or SendBack.

It is possible that same Defect no. might have Fixed or SendBack more than 1 time.

Following are my requirement:

- There are some DefectNo. Which have duplicate entries (with different Action_name, Action_timestamp & Fixed Date).
- Get the latest entry for each DefectNo. (i.e. the latest Fixed Date) so that all Defect no become unique.
- Against each DefectNo. it should also get when was it Sendback (i.e the last date of Action_timestamp) and how many times it was sent back.

I know its quite confusing but with my level best I have clarified every thing above that what my requirement is?

Pls requesting you all to give me the solution as soon as possible.
I would be thankful to everyone for their effort.

Regards,

 

[Aladin Akyurek]

Sort the data on defect no.

In additional column (Flag), apply:

=(LOOKUP(DefectNo,DefectNoRange)=DefectNo)+0

Then run AutoFilter/Advanced Filter on the data with focus on additional column such that it displays records with value 1.

 

[smartbuilder2k]

Hey Aladin,

Can u pls elaborate ur solution. How & where to write this formla.Since i m new to such stuff pls provide solution stepwise with examples.
Please Help

Thanks,

 

[Aladin Akyurek]

Hey Aladin,

Can u pls elaborate ur solution. How & where to write this formla.Since i m new to such stuff pls provide solution stepwise with examples.
Please Help

Thanks,

That would require a small sample on your part to work with...