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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all, got a quandry here.

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


PaperCompanyTemplateFlag
1Aber ChronicleJGUK
2Aber ChronicleJGUK
3Ban GuardJGUKY
4Ban GuardJGUKY
5Biggle ChronicleJGUKY
6Biggle ChronicleJGUKY
7Biggle ChronicleOMUK
8Biggle ChronicleJGUKY
9Bolt NewsJGEU

<tbody>
</tbody>

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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top