Identify the very first occurrence of one specific value in a column

[okiedokie2017]

I have been struggling longer than I expected and would love to get some assistance on this task that I am doing.

I want to create a new column (column A), in which identifies the first occurrence of the number 1 in column B. If it is the first occurrence, it would be blank, for the rest it would be "X".


I have been trying multiple formulas like:

_ COUNTIF($B:$B,1)>1
_IF(MIN(IF($B$:$B$=1, ROW($B$:$B$)))>0,"","x")
_{=INDEX($B$:$B$,MATCH(TRUE,$B$:$B$<>0,0))}

However, nothing is giving what I was looking for. I have have multiple 1 in column B, I only want to identify the first 1 and make it blank, and put "x" for the remaining 1.

Thanks so much

 

[Peter_SSs]

Like this, copied down?

Excel Workbook
	A	B
1		6
2		3
3		1
4		2
5		5
6	x	1
7	x	1
8		2
9	x	1
Skip First

 

[okiedokie2017]

Hello,

thank for the reply, but what I was looking is for A7 and A9 to be blank as well, as they are not the first occurrence

 

[Peter_SSs]

Hello,

thank for the reply, but what I was looking is for A7 and A9 to be blank as well, as they are not the first occurrence

Neither is A6.

 

[okiedokie2017]

Oh,

I meant I want to mark the very first occurrence of "1" to be X, and making the rest blank.

 

[Rick Rothstein]

Oh,

I meant I want to mark the very first occurrence of "1" to be X, and making the rest blank.

Then you just have to change the >1 test in Peter's formula to =1 like this...

=IF(AND(B1=1,COUNTIF(B$1:B1,1)=1),"x","")

 

[Peter_SSs]

I meant I want to mark the very first occurrence of "1" to be X, and making the rest blank.

Well that is in line with your thread title, but very much at odds with what you actually wrote.

If it is the first occurrence, it would be blank, for the rest it would be "X".