How to find if a text is higher than another text in groups

[Stewie12345]

I've got different groups in excel highlighted using a helper column and Conditional Formating. I now need to know if my company is before or after another company in each group based on the year. I can't think of a nested if statement, can anyone help please? Thanks in advance. Below is an example of my sheet and what I need. I am only lookin for the statement to say before or after for my company if we are before or after Company A based on the years owned. (Two Constants)

group 1

Unique identifier 1 2014 My Company
Unique identifier 1 2013 Company A
Unique identifier 1 2012 Company B
Unique identifier 1 2011 Company C

Unique identifier 2 2014 Company A
Unique identifier 2 2013 Company E
Unique identifier 2 2012 My Company
Unique identifier 2 2011 Company Z

And so on and so forth....

[Richard Buttrey]

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

That said and assuming the years are always sorted as you show then in D1 copied down

Please Login or Register  to view this content.

Otherwise upload the workbook.

[Stewie12345]

Thank you very much for your response. This kind of works. I need the formula to say before if company A owns say the 2013 year and my company owns the 2011 year and vice versa. If company A owns 2011 and I own the 2013 year for another grouping I need the formula to tell me after. Can this formula do that? Thank you for all your help with this. I truly appreciate it.

[Stewie12345]

and the years are random, the companies own all different years for each grouping so nothing stays the same.

[Stewie12345]

but all groupings have 2009-2015 in order

[Richard Buttrey]

but all groupings have 2009-2015 in order

We need clarification. The best thing is to upload a sample workbook. We don't need zillions of rows but we do need to have enough so that it is representative of all permutations. Manually add alongside your data the results you expect.

[Richard Buttrey]

Thank you very much for your response. This kind of works. I need the formula to say before if company A owns say the 2013 year and my company owns the 2011 year and vice versa. If company A owns 2011 and I own the 2013 year for another grouping I need the formula to tell me after. Can this formula do that? Thank you for all your help with this. I truly appreciate it.

Did you try the formula with your data?

[Stewie12345]

Sorry for the late response, I had to cut down the sheet to be able to upload it. No, unfortunately it doesn't work. I am trying to upload the sheet but cant find the upload link? Also, I need to know every time my company "CB International Investments" is before or after "INA Group" for each group based on year. Thanks for your help again with this.

[Richard Buttrey]

Hi,

The paperclip upload icon doesn't work - no don't ask!
You need to choose the 'Go Advanced' Option then look below the post area for the Manage Attachments option and take it from there.

[Stewie12345]

I have attached it. Column E is where I typed in what I need. Thank you again!

[Stewie12345]

Is there a way to Do this? If not I understand, I thought I would give it a try. It is probably tough when the information is all over the place.

[Richard Buttrey]

Would you explain the rule for deciding whether your company is a before or after

For instance why in group 4 is E23 & E24 Before? There is a year 2009 in that group so logic would suggest it's After

Why is there no result in E10:E11

Your post #5 said the years were in order but your example shows otherwise.
I need to understand exactly how you decide. And no doubt if the data was sorted by group and then by year the formula would be much simpler. So ist it permitted to sort the data first.

Are the groupings exclusive. i.e. nothing in one grouping affects the results of another. Your post#3 suggests otherwise and that the years for one grouping affect the results in another group.

I also need to know what you mean by Company A 'owning' a year. In your actual data what's the equivalent of Company A?

[Stewie12345]

There is no before or after when there is not INA and CB International on the same grouping. Also, CB is before when my company owns an older year than INA does and vice versa. I just want to know my position on each grouping when INA is on the same grouping and if I own an older year or newer year per grouping. There is no rhyme or reason per grouping on the years per group. I could own any year and so could INA which is why it would help me have a filterable column which tells me if I own a newer or older year when INA owns a year on a grouping. Is this possible? Thank you for all your time and help with this!

[Richard Buttrey]

What about the sorting? Is it permissible to sort years (ascending) within Group?

[Stewie12345]

yes, my apologies if they aren't, I was rushing to cut and paste and put them in another sheet to upload and didn't check if the data got missorted. It should have been sorted in ascending order per grouping. There are over 1000 groupings so I was hoping for a formula to save me time instead of me going manually and checking each grouping if we owned a previous year or newer year than INA when INA owned a year on the same grouping as us.

[Stewie12345]

Here is a fixed version of the file with the years in ascending order.

[Richard Buttrey]

Hi,

I was already using the earlier file you sent which I have sorted by Group/Year

I'm attaching two solutions albeit they both use the same principle

1.
Columns G:I are helper columns. I've added a formula in F2 copied down

Please Login or Register  to view this content.

2.

Please Login or Register  to view this content.

#1 has the advantage of being easier to understand and adjust should you spot any anomalies elsewhere in your data.

[Stewie12345]

THANK YOU!!!! You are AWESOME! Seriously, thank you so much!!!!