Comparing multiple columns by occurrences of text using IF and LARGE

[olaus]

This is in Power Pivot and I am as you may see, a novice when it comes to writing functions.

The columns I am trying to use in the formula are MARKET, BRAND POSITION, ShowAllCost as well as Newcolumn.

I want to compare all ShowAllCost in a certain MARKET and change all BRAND POSITION attached to that MARKET except for the seven highest ShowAllCost in a certain MARKET to "Others" in NewColumn.

This is my start:

=IF(AND([MARKET]=Bubblegum)((LARGE([ShowAllCost];7))(NewColumn;"Others")

The best outcome would be that I wouldn't have to make more than one formula to scan all the different names in MARKET when looking for BRAND POSITIONs to call "Others" in NewColumn.

What am I doing wrong and what should I write?

Thanks!

[mcmahobt]

Could you post a sample workbook for us to look at? Although posting images is easy, nobody likes recreating data to test possible solutions on

[olaus]

Thanks for the swift reply! Here's the file:

ExampleSorting.xlsx

[mcmahobt]

I'm not sure if I'm missing something, but I'm not seeing a NewColumn heading, either in your attached image or your example file. Along with this, what do you mean by you want to "change all BRAND POSITION attached to that MARKET?" Changing data needs to be done manually, if not then by some VBA. The easiest manual way would be through filtering.

[olaus]

What I meant to say is that I want those with 8 or below in ShowAllCost value don't get their BRAND POSITION written, but instead it will say "Others" in the NewColumn. NewColumn is just Column that needs to hold the values from BRAND POSITION, both the new "Others" as well as their original BRAND POSITION if their value is 7 or above in ShowAllCost for a specific MARKET.

[mcmahobt]

I took a stab at what you may be after anyways. See attached; the green cell in I2 is where you input the Market you are looking for, and your results are in columns K:P.

[mcmahobt]

Your statement just seemed to contradict itself.

For clarity, this is what you want:

A new column (column F) that will check column E, and return "Other" if the value in column E is less than or equal to 8; otherwise, return the corresponding value in Brand Position.

What is throwing me for a loop is where you mention the value of 7 or above...that would conflict with your previous statement.

[olaus]

Ohh sorry, trying to explainstuff isn't my strongest point I'm afraid. The the seven highest numbers in each market are supposed to keep their names.

At the moment I am trying to go around this issue by making a sheet that first goes through each market and checks the seventh highest number using large and then using an if-statement to determine the seven highest numbers, the rest get renamed to Others.

[mcmahobt]

I think this makes more sense now. The best way to do this without over complicating things would be to add a helper column (which you can hide). See attached for possible solution. Column G returns the top 7 values in column E, based on their ordinal position in column A.

If this isn't what you hoped in terms of a solution, please re upload a sample workbook, but include how you would want your data to look.

[olaus]

Thanks that's exactly what I needed!

I also had some success by using LARGE to determine the seventh highest number and through each market separately in order to rename some brand positions to "Others" and then adding them to the data model that I have.

But yeah, thanks a lot for the help your way was more smooth than mine!