Giving a duplicate cell a number

say in column A i had a cell which had "Horse" in it , and it said "Horse" in randown cells in column A , what formula could i use in column B so that everytime it recgnised "Horse" in cell A is would give it a unique number

ie Horse featured in cells
A1
A4
A7
A20

So in the coressponding cell in column B it would have 1,2,3,4 etc
A1 = HORSE B1=1
A4 = HORSE B4=2
A7 = HORSE B7=3

Thanks for any help

I'm sure it can be done in one column but I'll give a solution in two
columns. Of course you can hide the middle one.

In B1:
=IF(A1="horse",1,0)
In C1:
=IF(B1=1,1,"")
In B2:
=IF(A2="horse",B1+1,B1)
In C2
=IF(B2=B1,"",B2)

Copy B2 and C2 down

--
Kind regards,

Niek Otten


"scottymelloty" <scottymelloty.1zkdva_1133781601.2394@excelforum-nospam.com>
wrote in message
news:scottymelloty.1zkdva_1133781601.2394@excelforum-nospam.com...
>
> say in column A i had a cell which had "Horse" in it , and it said
> "Horse" in randown cells in column A , what formula could i use in
> column B so that everytime it recgnised "Horse" in cell A is would
> give it a unique number
>
> ie Horse featured in cells
> A1
> A4
> A7
> A20
>
> So in the coressponding cell in column B it would have 1,2,3,4 etc
> A1 = HORSE B1=1
> A4 = HORSE B4=2
> A7 = HORSE B7=3
>
> Thanks for any help
>
>
> --
> scottymelloty
> ------------------------------------------------------------------------
> scottymelloty's Profile:
> http://www.excelforum.com/member.php...fo&userid=3808
> View this thread: http://www.excelforum.com/showthread...hreadid=490695
>

Thanks !!! , i can get it working from that, many thanks for the help.

You can use conditional formatting in column B to hide cells. Than you don't
need column C

--
Kind regards,

Niek Otten

"scottymelloty" <scottymelloty.1zkgna_1133785200.6311@excelforum-nospam.com>
wrote in message
news:scottymelloty.1zkgna_1133785200.6311@excelforum-nospam.com...
>
> Thanks !!! , i can get it working from that, many thanks for the help.
>
>
> --
> scottymelloty
> ------------------------------------------------------------------------
> scottymelloty's Profile:
> http://www.excelforum.com/member.php...fo&userid=3808
> View this thread: http://www.excelforum.com/showthread...hreadid=490695
>

How about a single column formula:

=IF(A1="Horse",COUNTIF($A$1:A1,"Horse"),"")
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"scottymelloty" <scottymelloty.1zkgna_1133785200.6311@excelforum-nospam.com>
wrote in message
news:scottymelloty.1zkgna_1133785200.6311@excelforum-nospam.com...

Thanks !!! , i can get it working from that, many thanks for the help.


--
scottymelloty
------------------------------------------------------------------------
scottymelloty's Profile:
http://www.excelforum.com/member.php...fo&userid=3808
View this thread: http://www.excelforum.com/showthread...hreadid=490695