formula redo

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,553
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
I've made the assumption your data starts in K1 you can ammend accordingly:

Code:
=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
 
Last edited:
Upvote 0
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)

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,312
Messages
6,165,278
Members
451,949
Latest member
bovacik

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