Help with formula for filtering or conditional formatting

[mikey1984]

Hi Guys,

I've done some searching but I can't work out how to filter a list the way I would like.

1111/1
1111/1
1111/1
1111/2
1111/2
1112/1
1113/1
1113/2
1113/3

If I have a list like the above example, I'd like to be able to filter to only show the newest version of each number.

So in the above example, I would be left with:

1111/2
1111/2
1112/1
1113/3

The reason there are multiple of some lines is that these are quote numbers and they have multiple parts in them, each part has its own line.
However, if we amend the quote then it gets a new version number i.e. 1111/1 changes to 1111/2.

I need to find the value of jobs quotes in a given month and I don't want to include obsolete quotes in the value.

Can anyone help?

[tim201110]

not clear

a given month

are there any dates?

[mikey1984]

Yes, there is some date a few columns over in the real data.

[mikey1984]

Although the date does'nt always change between versions

[tim201110]

OK, how does one can identify a new entry?

[mikey1984]

new entries are added to the spreadsheet straight from our MRP system.
so there is usually another quote number between versions, like this:

1111/1 01/01/2018
1111/1 01/01/2018
1112/1 01/01/2018
1111/2 02/01/2018
1111/2 02/01/2018

I hope I'm being clear enough, I'm sorry if not.

[JeteMc]

Hello mikey1984 and Welcome to Excel Forum.
This proposed solution accomplishes filtering by means of a process.
Step one: parse the number and version using (respectively):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Step two: rank the versions within each number using*:

Please Login or Register  to view this content.

Step three: retrieve only the most recent version of each number using:

Please Login or Register  to view this content.

*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[mikey1984]

That is exactly what I needed, Thanks a lot.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.