Excel 2003: Advanced filtering question

[Kevinski]

I have a 10 column spreadsheet used to record quality improvement issues, who they're assigned to and what actions are being taken.

I have one row of data for every update that is made. There are currently 18 issues with a total of 72 updates.

I am interested in presenting a 1 line snapshot for every issue which only returns me the latest update line.

So I don't want to merely sort columns ascending by issue number and descending by update date - that would still give me every line for every issue.

Key columns are:
A = Issue Reference #
I = Update Date
J = Details of what actions were taken on that date

....so I'm really after a line of data based on the last action date for that issue.

Any help appreciated.

 

[Beowulf005]

The only way I can think of to too add a new column called "current" make it a yes/no box. Make the filter pick up only the yes answers.

This is something best suited for sharepoint.

GL
Brian

 

[KenWright]

Yep, additional column, and put in a formula that checks to see whether the reference number in Col A is different to the cell above in Col A. This should flag all the changes, and then just filter on that.

eg assuming your data starts in A1 with a header and then A2 down with ref numbers, in any other column in row 2, use

=IF(A2<>A1,"SHOW","")

Then just copy down and filter on "SHOW"

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------