formula redo

[kylefoley76]

I'd like to redo the following formula

=IF(OR(K1506="",COUNTIF([charts.xlsm]books!$A$1:$A$300,K1506)),"#",1)

So that a one will appear only one time next to a record in column K that does not appear in [charts.xlsm]books! column a. in other words, once a record appears in k it will appear several other times, i only want a one to appear one time for each new record.

 

[Comfy]

I've made the assumption your data starts in K1 you can ammend accordingly:

=IF(OR(K1="",COUNTIF([charts.xlsm]books!$A$1:$A$300,K1)),"#",IF(Countif($K$1:K1,K1)>0,"",1))

Sorry for typo had results the wrong way round

 

[kylefoley76]

thanks for helping me out but that didn't work

say you have in one column

green
green
green

and green does not appear in the targeted list, then i want a 1 to appear only next to the first green. right now, it's appearing next to all greens

If you want, that formula is built for the following macro. If you want, you could rearrange that macro so that it only writes down one instance of a new term which has a 1 next to it. (i hope you understand what i mean, because i admit that i'm being vague)

Sub FindThetwo()
Dim LR As Long
Dim i As Long
Dim thisFile As String
Dim thatFile As String
Dim Temp

thisFile = "charts.xlsm"
thatFile = "Time.xlsm"

Windows(thatFile).Activate
    Sheets(1).Select
    LR = Range("k" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
        If Range("L" & i) = 1 Then
            Temp = Range("K" & i)
            Windows(thisFile).Activate
                Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = Temp
            Windows(thatFile).Activate
        End If
    Next i

End Sub

 

[Comfy]

Can you paste the formula that you have used.

It should only put a 1 next to the first colour that is mention as per:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">green</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">green</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">green</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">red</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">yellow</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">blue</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">blue</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">yellow</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">red</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A1,A1</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A2,A2</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A3,A3</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A4,A4</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A5,A5</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A6,A6</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A7,A7</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A8,A8</font>)>1,"",1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$1:A9,A9</font>)>1,"",1</font>)</td></tr></tbody></table></td></tr></table><br />

Just spotted another typo.

Please change the original formula to include >1 instead of >0

 

[kylefoley76]

ok, thanks, that did it