Spill formula with COUNTIF

[MiniFav]

Hey All,

I have the following example.

First Column contains positions which can often be duplicated.

In the second column I have a formula which finds the duplicates, ignores the first instance and then puts "IGNORE" next to the subsequent instances.
I then use this with conditional formatting to grey them out.

In the thrid column, I wish to have column two but in a SPILL formula so I can just hide Row 3 and not worry about anyone deleting the formula and corrupting the data.
This is where I am having issues and I cannot figure out how to ignore the first instance of the duplicate.

Can anyone help?

Hard_time_template_V4_-_Clean.xlsx
	B																																	AI	AJ
2	POSITION																																	MAC IGNORE ROWS	MAC IGNORE SPILL
3
4	Filter Element - 5250HG
5	Filter Element - 5251HG
6	Filter Element - 5252HG
7	Filter Element - 5253HG
8	Filter Element - 5254HG
9	Filter Element - 5255HG
10	Filter Element - 5256HG
11	Filter Element - 5257HG
12	SAFETY VALVE - 316HL
13	SAFETY VALVE - 317HL
14	SAFETY VALVE - 318HL
15	ULB BATTERY - RK-BAT
16	BATTERY - 2PB1																																		IGNORE
17	BATTERY - 2PB1																																	IGNORE	IGNORE
18	BATTERY - 2PB2																																		IGNORE
19	BATTERY - 2PB2																																	IGNORE	IGNORE
20	BATTERY APU - 2PB3																																		IGNORE
21	BATTERY APU - 2PB3																																	IGNORE	IGNORE
Sheet2

Cell Formulas
Range	Formula
AJ3:AJ1000	AJ3	=IF(NOT(ISBLANK($B$3:$B$1000)),IF(COUNTIF($B$3:$B1000,$B3:$B1000)=1,"","IGNORE"),"")
AI3:AI21	AI3	=IF(OR(ISNUMBER(B3),B3>""),IF(COUNTIF($B$3:$B3,$B3)=1,"","IGNORE"),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:N1000	Expression	=IF($AI3="IGNORE",TRUE,FALSE)	text	NO
B3:B1000	Expression	=IF(AND(NOT(ISBLANK($C3)),NOT(ISBLANK($D3)),NOT(ISBLANK($E3)),NOT(ISBLANK($F3)),NOT(ISBLANK($G3)),NOT(ISBLANK($H3)),NOT(ISBLANK($I3)),NOT(ISBLANK($J3)),NOT(ISBLANK($K3)),NOT(ISBLANK($L3)),NOT(ISBLANK($M3)),NOT(ISBLANK($N3))),TRUE,FALSE)	text	NO

 

[Fluff]

How about

=LET(a,B3:B1000,IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a))*(a<>""),SEQUENCE(ROWS(a),,,0))>1,"Ignore",""))

 

[Fluff]

You can also get rid of the helper column & just use conditional formatting

Fluff.xlsm
	B
1
2	POSITION
3
4	Filter Element - 5250HG
5	Filter Element - 5251HG
6	Filter Element - 5252HG
7	Filter Element - 5253HG
8	Filter Element - 5254HG
9	Filter Element - 5255HG
10	Filter Element - 5256HG
11	Filter Element - 5257HG
12	SAFETY VALVE - 316HL
13	SAFETY VALVE - 317HL
14	SAFETY VALVE - 318HL
15	ULB BATTERY - RK-BAT
16	BATTERY - 2PB1
17	BATTERY - 2PB1
18	BATTERY - 2PB2
19	BATTERY - 2PB2
20	BATTERY APU - 2PB3
21	BATTERY APU - 2PB3
22
23
24
25
26
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B1000	Expression	=COUNTIFS(B$3:B3,B3)>1	text	NO

 

[MiniFav]

How about

=LET(a,B3:B1000,IF(MMULT((SEQUENCE(ROWS(a))>=SEQUENCE(,ROWS(a)))*(a=TRANSPOSE(a))*(a<>""),SEQUENCE(ROWS(a),,,0))>1,"Ignore",""))

This is fantastic! Thank you (now time for me to break this down and learn it).

 

[Fluff]

You're welcome & thanks for the feedback.
Personally I would ditch the helper & just go with the simpler CF formula.