Here's a doozy - lookup against a list, determine number of same names, then apply a different formula to each instance.

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
491
Office Version
  1. 2010
Platform
  1. Windows
I feel like I'm abusing this forum with the amount of questions I have.


On Column A there is a list 300-strong of paper names. You can have things like:


ABC
1PaperTemplate SizeIdeal Result
2Banbury Guardian - Mon
16x9Promo
3Banbury Guardian - Tue
17x9UK
4Banbury Guardian - Fri
17x9EU
5Clacton & Frinton Gazette
17x9UK
6Eastleigh Times
20x4UK
7Gloucester Echo - Tue
17x6UK
8Gloucester Echo - Thu
17x6Promo

<tbody>
</tbody>


These papers and templates need to have different tours assigned to them, I think there's 3 I need to worry about:

UK Tours
EU Tours
Promo Tours


Every week we choose a tour to put in Promo sizes (16x9 and 17x6) and if a paper has this size available in column B, it should be a promo size.

Otherwise for any paper that only features once (such as Eastleigh Times and Clacton) we would want to feature a UK tour.

For any paper that features multiple times (either same name or multiples in a week), we want one of the features to be EU.

So Gloucester Echo would have 1 UK and one EU.

To illustrate this, I think we have a maximum of 5 same papers in any one week, so here's what I would want, the number is the number of features:

1 - 1 UK
2 - 1 UK, 1 EU
3 - 2 UK, 1 EU
4 - 3 UK, 1 EU
5 - 4 UK, 1 EU

And anywhere there is a 17x6 or a 16x9 in the Template Size, this should be a Promo tour.

So who's up to the challenge?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-ge...ith-different-outcomes-based-on-criteria.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,446
Messages
5,547,967
Members
410,820
Latest member
Prepost
Top