How to show only the last

[sveinhelge.urdal]

I have a report from an external system showing a long list of documents. List shows document numbers, title revision and so on.
Looking like this

Document Number	Revision	Issue reason	Status	Title
10003276804​	1​	Issued for CR	2​	GENERAL ARRANGEMENT
10003276804​	2​	Re-issued CR	2​	GENERAL ARRANGEMENT
10003276804​	3​	Re-issued CR	1​	GENERAL ARRANGEMENT
10003276805​	1​	Issued for CR	3​	GENERAL ARRANGEMENT
10003276805​	2​	Re-issued CR	2​	GENERAL ARRANGEMENT
10003276805​	3​	Re-issued CR	2​	GENERAL ARRANGEMENT
10003276805​	4​	Issued for CR	2​	GENERAL ARRANGEMENT
10003276805​	5​	Re-issued CR	4​	GENERAL ARRANGEMENT

This list consist of about approximately 900 documents and various revisions.

How can I make Excel show only the last revision on the document numbers?

In the example above I want it to show:

Number	Revision	Issue reason	Status	Title
10003276804​	3​	Re-issued CR	1​	GENERAL ARRANGEMENT
10003276805​	5​	Re-issued CR	4​	GENERAL ARRANGEMENT

 

[Peter Bartholomew]

You could always get Office 365 in which case the XLOOKUP function
= XLOOKUP( @Number#, Table1[Number], Table1[[Revision]:[Title]], , ,-1)
will return the final instance of a match as well as returning multicell ranges.

Otherwise you might be stuck with
= LOOKUP( 2, 1 / (Table1[Number]=Number), Table1[Revision] )
and similar for other columns.

 

[shrivallabha]

If your data is sorted on following fields
1. Number
2. Revision

Then you can use a simple formula like below in an empty column
=IF(AND(A2=A3,B2<B3),"","Last Record")
and then copy it down. You can then filter on "Last Record".

Edit: Your requirement of finding last record reminded me of my struggle. It was fairly the same thing. Only difference is I can now write Excel formula on my own (mostly that is). Sorry about digressing from the topic but couldn't help it.

Congrats Shrivallabha for 1000 Post

Congrats @shrivallabha for your 1000th Post. Thanks for sharing your knowledge with us. Keep up the good work. Regards,

chandoo.org

 

[vletm]

sveinhelge.urdal
Other way ...
Select cell B1 and it would change output
Select cell B1 again and it would change output

 

[p45cal]

A pivot table should do this, see attached.
Add the revision field to the Values area as well as the Rows area, then Filter the Revision column by Values, Top 10… and choose Top 1:


then:

finally:

 

[p45cal]

If your data is sorted on following fields
1. Number
2. Revision

Then you can use a simple formula like below in an empty column
=IF(AND(A2=A3,B2<B3),"","Last Record")
and then copy it down. You can then filter on "Last Record".

Both my pivot table suggestion and your suggestion will hiccup if there are duplicate rows, but yours won't with a little tweak:
=IF(AND(A2=A3,B2<=B3),"","Last Record")

 

[shrivallabha]

Both my pivot table suggestion and your suggestion will hiccup if there are duplicate rows, but yours won't with a little tweak:
=IF(AND(A2=A3,B2<=B3),"","Last Record")

Ah, you are correct. Honestly I had not considered this situation / possibility. Since we are dealing with revisions, I thought they'd be unique.

As an aside, the way you present solution is quite impressive. @Hui @r1c1 I think some of these can be Chandoo blog post.