Log In
Register
Home
Active Member
January 1, 2021
Offline
I am finding myself circling around and not getting to a good method of what I need. Any guidance appreciated.
I have one cell that contains a version, which can be text or number as it depends on install: 8.2.3a
I have a second cell that contains target versions but they are based on the installed branch: 8.1.2k / 8.2.3a / 9.0.1c
My goal is to compare the installed version to the target version and display if it is below or equal to the same branch within the delimited data.
So, 8.2.3a would not be compared to 8.1.2k as it is not same branch
8.2.3a would be compared to 8.2.3a and return equal as it is the same branch
8.2.3a would not be compared to 9.0.1.c as it not the same branch
The first two digits, separated by decimal, indicate branch.
Thanks in advance for any help.
October 5, 2010
Offline
Hi Scott,
Please provide some sample data files and an example of how you want the results to look.
Regards
Phil
Active Member
January 1, 2021
Offline
Thanks for looking Phil. I hope what I am uploading is not too wordy or overly confusing. See the workbook for a general explanation and fields I am using..as well as sample formula.
VIP
Trusted Members
December 7, 2016
Offline
Hello Scott,
The solution I give here is using another approach and I am using exact match, not just the first three characters as that can give multiple matches. First I created a lookup table based on your data in columns C:E, then I have provided three different lookup methods to find the value I am after, in this example I have used the column headers as values.
I find this approach much easier to work with, than having to create complicated formulas.
See if it works out for your need.
Br,
Anders
Active Member
January 1, 2021
Offline
Thanks Anders. Unfortunately that is not what will help me, but thank you for your time! I am not able to create a table for this data as shown as it is a snippet of a larger overall set of instructions and dataset.
My ultimate goal is to compare the installed code to each of the three cells mga, rga and latest and return a status depending where it falls:
if installed code is < mga status 0
if installed code is >= mga status 1
if installed code is > mga and if installed is <= rga status 2
if installed code is > rga and = latest status 3
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Ok, I hope you get a solution that works.
Br,
Anders
July 16, 2010
Offline
Hi Scott,
Given that the position of the codes can be left, middle or right, I think you need to split the data into rows so you can compare them more easily. You can do this with Power Query > Split Column > by Delimiter > into Rows. See file attached.
I hope that points you in the right direction.
Mynda
1 Guest(s)
