[VBA] Reducing >1 "Y" flags to just one

[RockandGrohl]

Hi all, got a quandry here.

Let's say I have a table like the below:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Paper[/TD]
[TD]Company[/TD]
[TD]Template[/TD]
[TD]Flag[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Aber Chronicle[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aber Chronicle[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ban Guard[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ban Guard[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Biggle Chronicle[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Biggle Chronicle[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Biggle Chronicle[/TD]
[TD]OM[/TD]
[TD]UK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Biggle Chronicle[/TD]
[TD]JG[/TD]
[TD]UK[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Bolt News[/TD]
[TD]JG[/TD]
[TD]EU[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Y flag is assigned for the following criteria:

• Name appears >1
• Only advertising UK Template
• EU template applicable (not important here)
• Company is JG, not OM

The end result is we have a "Y" flag per applicable line, however it appears multiple times per paper. What my intention is to do is automatically change just ONE line from a UK to an EU template. As per the above example, Biggle Chronicle has 4 lines, three of them are "Y" flags, but I only want one of them to change (And preferably the bottom one as it will be a larger template or later in the week)


I really can't figure out a way to do this, sensibly or otherwise, but it's gotta happen in VBA as my helper columns are getting out of control already.

Something that is annoying me is that "OM" lines can sometimes intersperse the JG lines, so I can't just look directly at the row below.


Maybe do an IF statement with a countif, so if total number of X tours with a Y is >1, then clear the activerow "Y" flag, drop down a row and count again?

 

[DanteAmor]

Try this

<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:179.64px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Paper</td><td >Company</td><td >Template</td><td >Flag</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Aber Chronicle</td><td >JG</td><td >UK</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Aber Chronicle</td><td >JG</td><td >UK</td><td >Y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Ban Guard</td><td >JG</td><td >UK</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Ban Guard</td><td >JG</td><td >UK</td><td >Y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Biggle Chronicle</td><td >JG</td><td >UK</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Biggle Chronicle</td><td >JG</td><td >UK</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Biggle Chronicle</td><td >OM</td><td >UK</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Biggle Chronicle</td><td >JG</td><td >UK</td><td >Y</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Bolt News</td><td >JG</td><td >EU</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(AND(B2="JG",C2="UK",COUNTIFS(A2:$A$10,A2,B2:$B$10,"JG",C2:$C$10,"UK")=1),"Y","")</td></tr></table></td></tr></table>




Copy the formula down