Creating new column that compare two fields

[#1]

Hi, I have this problem:

I have two columns, Year and Company Name. I need to create a filter   that those companies and the Years I can sort by their situation.

So, i the company it's present in this year, and the past year it's not present, I need to to appear on the situation that this company has "Started" their operations. If the company is present this year, and it's present in the past year i need to appear that it "Kept" their operations. And if it's present in the past year, but it's not the following year, i need to appear that the company is "Gone".

Please, i have to say that i need it to be an generic answer for the years, because i have many years to compare.

The final table i want to be like this:

Year               Company Name               Situation

2005                    A                                   Started

2006                    A                                   Kept

2007                    A                                   Gone

2011                    B                                   Started

2012                    B                                   Kept

2013                    B                                    Kept

Thank you all (:

[#2]

HI ,

       I got your question but i need some more clarification regarding the status for "Gone".

Do we need to consider the Next Year by default .

Or Can you provide the same data so that i can try to get the Desired output.

Thanks

[#3]

How can I attach a document so you can see it better??

Yes, we have to compare the year in advance year with the past one, if it's there in the past and in the advanced, it's "Kept", if it's not there in the past year, so it's "Started", and if it's there in the past one, but not in the following year, it will be "Gone". The filters will be taken only like this, two by two.

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#5]

Thank you, it works perfectly!!!!