Stop returning results after the first match.

[RockandGrohl]

Hoping this is possible.

In B I have a list of papers

In X there is an IF(AND formula which tells me whether a product type (EU) is suitable for the paper or not.

Where there are multiple papers, I'm seeing multiple EU's - I only want one, however.


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Preferred X Result[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Paper Name[/TD]
[TD]Count[/TD]
[TD]UK?[/TD]
[TD]EU?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl28935, width: 257"]Ashbourne News[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Banbury Guardian[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Banbury Guardian[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD]EU[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl28937, width: 257"]Barnstaple Chronicle[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Barrow Evening Mail - Tue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Barrow Evening Mail - Tue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD]=IF(AND(V5>1,B5=B4,W5="UK",X4<>"EU"),"EU","")[/TD]
[TD]EU[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Barrow Evening Mail - Wed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Barrow Evening Mail - Wed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD]EU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD]Barrow Evening Mail - Sat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl28937, width: 257"]Bedford Times & Citizen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 257"]
<tbody>[TR]
[TD="class: xl28937, width: 257"]Bedford Times & Citizen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]UK[/TD]
[TD]EU[/TD]
[TD]EU[/TD]
[/TR]
</tbody>[/TABLE]

Explanation:

The formula looks to see if the count of papers is more than 1 (if not, "UK") it then looks to see if the paper name is the same as the one below, it checks if W5 is UK which is probably redundant, then it says the cell above the current cell doesn't say EU, then this cell can say EU...

The problem is that where a paper can list 5 times, like Barrow Evening Mail, I get two instances of EU... I only want one however... Any ideas?

Thank you.

 

[Eric W]

There are multiple issues here, including the fact that adding a weekday to the paper name makes it a unique name, which you don't seem to want. But you might try this formula in X2 and copy down:

=IF(AND(V2>1,W2="UK",COUNTIFS(B$1:B1,LEFT(B2,12)&"*",X$1:X1,"EU")=0),"EU","")

 

[RockandGrohl]

There are multiple issues here, including the fact that adding a weekday to the paper name makes it a unique name, which you don't seem to want. But you might try this formula in X2 and copy down:

=IF(AND(V2>1,W2="UK",COUNTIFS(B$1:B1,LEFT(B2,12)&"*",X$1:X1,"EU")=0),"EU","")

Hi Eric,

I've created a helper column which takes the characters to the left of the dash, that seems to do the trick and allows me to differentiate between different papers. I'll try this after I get settled in today, thanks!

 

[RockandGrohl]

There are multiple issues here, including the fact that adding a weekday to the paper name makes it a unique name, which you don't seem to want. But you might try this formula in X2 and copy down:

=IF(AND(V2>1,W2="UK",COUNTIFS(B$1:B1,LEFT(B2,12)&"*",X$1:X1,"EU")=0),"EU","")

Hi Eric,

I['m having trouble understanding why you locked B1 and X1 for the row?

My data starts on line 3 (so formula must be placed in X3) and I was trying to move things down and then ran across that - was scratching my head.

Also I shouldn't need the (LEFT) side of the formula now as I have a list of unique papers without the Mon, Tue, Wed etc:

[TABLE="width: 196"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Boston Standard[/TD]
[/TR]
[TR]
[TD]Boston Standard[/TD]
[/TR]
[TR]
[TD]Bourne Local[/TD]
[/TR]
[TR]
[TD]Bradford Telegraph & Argus [/TD]
[/TR]
[TR]
[TD]Bradford Telegraph & Argus [/TD]
[/TR]
[TR]
[TD]Bradford Telegraph & Argus [/TD]
[/TR]
[TR]
[TD]Bridlington Free Press[/TD]
[/TR]
[TR]
[TD]Brighouse Echo[/TD]
[/TR]
[TR]
[TD]Brighton & Hove Independent[/TD]
[/TR]
[TR]
[TD]Buckinghamshire Advertiser[/TD]
[/TR]
</tbody>[/TABLE]

 

[RockandGrohl]

There are multiple issues here, including the fact that adding a weekday to the paper name makes it a unique name, which you don't seem to want. But you might try this formula in X2 and copy down:

=IF(AND(V2>1,W2="UK",COUNTIFS(B$1:B1,LEFT(B2,12)&"*",X$1:X1,"EU")=0),"EU","")

=IF(AND(V3>1,W3="UK",COUNTIFS(B$2:B2,B3,X$2:X2,"EU")=0),"EU","")

Aha, I see now. The first lock just pins to the top and allows it to only lookup down the range as far as the formula has progressed. That's very clever!

95% of the way there now, thanks!

 

[Eric W]

Wish I could take credit for that, but I learned that trick from this forum myself a while back. But I'm glad I could pass it on! Good luck with the rest of your workbook!

 

[RockandGrohl]

Wish I could take credit for that, but I learned that trick from this forum myself a while back. But I'm glad I could pass it on! Good luck with the rest of your workbook!

https://www.mrexcel.com/forum/excel...criteria-index-match-large-combo-formula.html

This should be the last one in this saga, then I'm ready to have 8 hours work auto-select itself in about 1 hour!

That was a clever trick indeed - some very intelligent minds on this forum. I bet the developers of Excel get a kick out of the creative ways people work around limitations in the software!