I could use some help finding the first occurrence of a word and number is a given column.
In Col B I would like "first occurrence" to appear beside the first row using "Amber" from col A. Then in Col E, "first occurrence" to appear beside first row using # 7 from col D. I don't want any indication for the reoccurrences of "Amber" or "7" in subsequent rows.
Thanks in advance for your help.
Same formula will work for both cases:
enter the search value in B1 (or E1)
select cell B2
Type, or copy and paste from here, the formula:
=IF(AND(COUNTIF(A$2:A2, B$1)=1, A2=B$1),"First", "")
select B2, copy
select cell B2 thru the end of column B, paste
repeat for column E
Same formula will work for both cases:
enter the search value in B1 (or E1)
select cell B2
Type, or copy and paste from here, the formula:
=IF(AND(COUNTIF(A$2:A2, B$1)=1, A2=B$1),"First", "")
select B2, copy
select cell B2 thru the end of column B, paste
repeat for column E
One way is to try something like this:
=IF(AND(COUNTIF(A,A2)>1,COUNTIF(A$1:A2,A2)<2),"first occurrence","")
Or, if your region uses , as a decimal separator:
=IF(AND(COUNTIF(A;A2)>1;COUNTIF(A$1:A2;A2)<2);"first occurrence";"")
This counts the occurrences in the whole column, and then counts the occurrences "so far" in the column.
If there is more than one occurrence in the whole column and there is only one (<2) occurrence so far, then the entry is flagged as the first occurrence.
I assume in the numbers column you would want to flag the first occurrence of 2 as well, right?
SG
SG,
Thank you, it's perfect for needs.
First Instance (occurrence) of a Word or Number
