Need help filtering an excel file

I have a spreadsheet with many systems names in column A, but there are alot of duplicates. Problem these duplicates are there because in column B there are version numbers for each of the system names.

Now I need to remove any systems what have a version number of 17 and higher in column B, no these same system can have version numbers below 17, but is they have any numbers above 17, then I need to remove the system names completely regardless if they have version 17 and below.

I need to be left with system names that have version number 16 and below only.

Am I making sense. :)
rdefinoAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Here's how I'd put the formulas in your sample
example.xlsx
0
 
nutschCommented:
add a column with a formula like this:

=countifs(columnwithversionnumber,">=17",columnwithsystem,systemvalue)>0

e.g. if your version number is in column B and the system number in A, the formula in row 2 would be:

=countifs(B:B,">=17",A:A,A2)>0

and filter on true values

Thomas
0
 
rdefinoAuthor Commented:
So i used formula =countifs(B:B,">=17",A:A,A2)>0

But i show true for all types f versions and fales for all types. Still the duplicate names exists.

Not sure if I did it right.

Should i use both formulas or just one.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rdefinoAuthor Commented:
also, some of the version numbers show as below. we can rewove anything after the first octet.

20.0.1
3.6.17
25.0.1
19
21
23
23.0.1
25
27.0.1
3.6.17
3.6.17
3.6.17
3.6.17
0
 
nutschCommented:
OK, the easiest then is to add another column that will turn your version number into a main version number, with the following formula, and then use that column to check if the version number is above 17 or not.

=--LEFT(B2,IFERROR(FIND(".",B2)-1,LEN(B2)))

Thomas
0
 
rdefinoAuthor Commented:
I added that in a column and the number 12 appeared. What does that mean? I tried it on a number of different version and it always shows 12.

I have attached and example of the spreadsheet that I'm working with.
example.xlsx
0
All Courses

From novice to tech pro — start learning today.