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
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
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?
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
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"))
If the years are sorted with the max on the right, you can also try:
=IF(A2>RIGHT(B2,4)+0,"Greater",IF(A2<RIGHT(B2,4)+0,"Less","Equal"))
Thanks a lot, all of you
Thanks;
Bill
