Question on Filtering of multiple versions

[pieter1987]

Hi,

I have a question. I have an excel file with a long list of projects with versions of this list and I want to delete or hide all projects but the latest version. Is there a way to do this?

Below an example of the type over versioning. The idea is that I want to have a filter that takes all rows containing Project A 2.1, Project B 3, Project C and Project D (there are multiple rows having Project A 2.1 but I want them all to be filtered out except the once with Project A2 or A1).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Version[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2.1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much in helping me out with this struggle.

Best Regards,

Pieter

 

[sasha 99]

is the original project names are (A, B , C , D , ......) in this sequence or they have a different names ?

 

[pieter1987]

is the original project names are (A, B , C , D , ......) in this sequence or they have a different names ?

They have different names. But a project always has the same name throughout the list. They are made up names by the project themselves. The idea is that version 1 and version 2 will never have a different name.

 

[sasha 99]

I will assume that they are changing in irregular sequence , in this case you will put your first cell value A1 in C1


Then you will need to put this formula in cell C2 then drag and drop it down

=INDIRECT(ADDRESS((MATCH(C1,A:A,0)+COUNTIF(A:A,C1)),1))

Next you put this second formula in cell D1 then drag and drop it down as you did with the previous column

=MAX(INDIRECT("B"&(MATCH(C1,A:A,0)&":"&"B"&((MATCH(C1,A:A,0)+COUNTIF(A:A,C1)-1)))))

That's is it

 

[sasha 99]

IF it does not work properly or you counter a problem .. just let me know
Regards

 

[pieter1987]

Hi Sasha99,
Thank you very much for your try. I am sorry but your formula gives an error. Could you please put it in the file form me and return it to me? I put it on wetransfer.
https://we.tl/QcxbrLncvA

So the idea is that it should let me filter automatically on the last version of a certain index number if there is only 1 version it should show this version and otherwise it should show the latest (there are more then 1 row with this last version but i removed the other data for privacy issues) available.

Thanks a dozen!

Best Regards,
Pieter

 

[sasha 99]

Is that what you need ? .. i edited the file for you and extracting the latest version for each project .. take a look and tell me if that what you are really need
https://we.tl/rkmFBTjZg0

 

[pieter1987]

Thanks very much, that is what I need!

However, one last question is it possible to keep the multiple rows that are related to this version? Because the data that is behind these rows, is partner related. They are projects with different project partners. Therefore I would like to keep all 2S01-001 with version 1 for example (so the row should keep the duplicates and also the data in the other columns). I added some of the extra data to the file in the link to give you an idea about the file https://we.tl/7TNb4HQXLZ (without providing detail about the organisations as this is privacy sensitive).

Thanks already for all your help!

 

[sasha 99]

I create a new worksheet for the desired output , you will find the following in this worksheet
In first column i put the a cell reference for each row that contain the latest version of each index number
In second column i put the total number for the duplicated latest version of each index number
From Third to the last Column you will find the matching cell for each Unique Reference exist in 1st column

Note:: I renamed the Master sheet to "PartnerLevelStatistics" .. I Just write it again without space , if it renamed later , all formula used in worksheet should be amended

This take more than 2 hour to accomplish .. hoping it will be useful for you

Best Regards

 

[sasha 99]

Here's a link with your file and the desired result

https://we.tl/eDb6EuXoYK

If it still not working .. just let me know