adapting IF-formula

[bV79]

Hello everybody. I'm new here. I'm from the Netherlands. I have a problem.
In the attachement you see in colom L a formula with a IF function including a NUMBER.IF function. All the "double" combinations must appear in colom L showed by the word "double". All the single combinations also must appear in colom L showed by the word "Single". The problem is: only the first of the double combination is showed by the word" double". I want the second also is showed by the word "double". How can I achieve that??

Note, I have a dutch version of excel. IF= ALS(dutch) and NUMBER.IF= AANTAL.ALS(dutch)

Thank you for helping me.

greetings.

[JieJenn]

In L2, put the $ in front of your range reference to lock the table. =IF(COUNTIF($J$3:$J$18,J2)>1,"double","single") then fill the formula

[FDibbins]

Hi, welcome to the forum

You need to "absolute" you range before you copy it...
=IF(COUNTIF(J2:J17,J2)>1,"double","single")
needs to be...
=IF(COUNTIF($J$2:$J$17,J2)>1,"double","single")

This fixes/freezes the range so that when you copy it down, it does not adjust.

Before you correct your formula, look at the formula in L2, then look at the formula in L3, L4 etc - see how the range changes each time?

[bV79]

@Jiejenn and Fdibbins. Thank you for your help. It's working.

[bV79]

Hello, the last step in this table is to filter al the red chassisnumbers in combination with the word"single".
Also, i want to filter al the normal chassisnumbers(black text) in combination with the word"single"
Red with single must be "fault".
Normal text(black) with single must be "good" See the attachement.
I have been puzzling with the formula CELL(colour) without any results.
Is there a formula that can help me??
Thank you for helping me.

Greetings.

[FDibbins]

Formulas cannot see color. What was the criteria for changing the color, maybe we can use that as a rule?

[bV79]

The red marked numbers are from another list then the normally marked numbers.