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

[RockandGrohl]

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:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Paper[/TD]
[TD]Template Size[/TD]
[TD]Ideal Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banbury Guardian - Mon
[/TD]
[TD]16x9[/TD]
[TD]Promo[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Banbury Guardian - Tue
[/TD]
[TD]17x9[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banbury Guardian - Fri
[/TD]
[TD]17x9[/TD]
[TD]EU[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Clacton & Frinton Gazette
[/TD]
[TD]17x9[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Eastleigh Times
[/TD]
[TD]20x4[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Gloucester Echo - Tue
[/TD]
[TD]17x6[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Gloucester Echo - Thu
[/TD]
[TD]17x6[/TD]
[TD]Promo[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[Fluff]

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.