Log In
Register
Home
July 17, 2019
Offline
Hello,
Please can you help me to compere two years as below.
Column A: contain one year only like 2018
Column B: Contain many years separated by | like 2015|2016|2020|1999
I want to add below flags in Column C.
Flag 1 : Greater "In case Column A Greater than all values in Column B"
Flag 2 : Less "In case Column A less than all values in Column B"
Flag 3: Equal "In case Column A Equal all values in Column B"
Please check below sample.
| Year | Relation Year Mentioned ?. | Is A>B? |
| 2020 | 2019|1999 | Greater |
| 2010 | 2014|2016|2018|2019 | Less |
| 2017 | 2017|2019 | Equal |
| 2020 | 2017|2018|2020 | Equal |
| 2020 | 2019|1999|2000|2003 | Greater |
| 2008 | 2016 | Less |
Thanks;
Bill
See Answer
Trusted Members
December 20, 2019
Offline
Does it have to be VBA? Attached is a very quick bodge before bed
Text to columns the years into D:I, then divided A by each split out year
then checked the number
Status column >
if 1 then Column A is Less than all the years in column B
if 0 then Column B is greater than all the years in column B
Anything else is either an exact or a mixture
As i say, quick and dirty, but might help
Trusted Members
Moderators
November 1, 2018
Offline
What if it's not greater than them all, less than them all, or equal to any of them? Eg you have 2017 in col A and 2016|2019 in column B?
July 17, 2019
Offline
Hi Purfleet,
Thank you for fast reply.
Please can you update it by comparing the Column A with the the Highest year in Column B.
Ex:
| Year | Relation Year Mentioned ?. | Status |
| 2015 | 2014|2016|2018|2019 | Less |
| 2020 | 2017|2019 | Greater |
| 2016 | 2016|2016 | Equal |
| 2018 | 2018 | Equal |
Thanks;
Bill
Trusted Members
Moderators
November 1, 2018
Offline
You could extract the max year with:
=MAX(INDEX(("0"&MID(B2,(ROW($1:$10)-1)*5+1,4))+0,))
into say C2 (assuming you never have more than 10 years in one cell). Then you can use a simple if formula like:
=IF(A2>C2,"Greater",IF(A2<C2,"Less","Equal"))
1 Guest(s)
