I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs to
be the order in which the color was selected relative to equivalent colors.
So the first €œred€� selected will be €œRed-1, next €œred€� selected will be
€œRed-2€�. This will happen to for each color. i.e€¦€¦€¦..

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

=COUNTIF($A$2:A2,A2)&IF(OR(VALUE(RIGHT(COUNTIF($A$ 2:A2,A2),2))={11,12,13}),"th",IF(OR(VALUE(COUNTIF( $A$2:A2,A2))={1,2,3}),CHOOSE(RIGHT(COUNTIF($A$2:A2 ,A2)),"st
","nd ","rd "),"th "))&A2

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first €œred€� selected will be €œRed-1, next €œred€� selected will be
€œRed-2€�. This will happen to for each color. i.e€¦€¦€¦..

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

If you can do with just numbers (that is, without the "st", "nd", "rd" and
color name designations), put this formula in B1 and copy down (change the
999 so that it is larger than the largest row you expect to fill down to).

=COUNTIF($A$1:$A1,A1)

Rick

Colour?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"T. Valko" wrote in message
...
Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

just brilliant

"T. Valko" wrote:

Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

just brilliant

"Bob Phillips" wrote:

=COUNTIF($A$2:A2,A2)&IF(OR(VALUE(RIGHT(COUNTIF($A$ 2:A2,A2),2))={11,12,13}),"th",IF(OR(VALUE(COUNTIF( $A$2:A2,A2))={1,2,3}),CHOOSE(RIGHT(COUNTIF($A$2:A2 ,A2)),"st
","nd ","rd "),"th "))&A2

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

???

Biff

"Bob Phillips" wrote in message
...
Colour?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"T. Valko" wrote in message
...
Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The
example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

<quote If you want the ordinal plus the color then it's more complicated:
</quote


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" wrote in message
...
Colour?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"T. Valko" wrote in message
...
Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The
example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

Color = the text string red, white, etc.

Biff

"Bob Phillips" wrote in message
...
<quote If you want the ordinal plus the color then it's more complicated:
</quote


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bob Phillips" wrote in message
...
Colour?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"T. Valko" wrote in message
...
Maybe this entered in B1 and copied down:

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

This will return the count of each entry.

If you want the ordinal plus the color then it's more complicated:

=IF(A1="","",COUNTIF(A$1:A1,A1)&IF(OR(MOD(COUNTIF( A$1:A1,A1),100)={11,12,13}),"th",LOOKUP(--RIGHT(COUNTIF(A$1:A1,A1)),{0,"th";1,"st";2,"nd";3, "rd";4,"th"}))&"
"&A1)

Biff

"JVANWORTH" wrote in message
...
I need to number random conditions as they occur in a column. The
example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B
needs to
be the order in which the color was selected relative to equivalent
colors.
So the first "red" selected will be "Red-1, next "red" selected will be
"Red-2". This will happen to for each color. i.e.....

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

I need to number random conditions as they occur in a column. The
example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first €œred€� selected will be €œRed-1, next €œred€� selected will be
€œRed-2€�. This will happen to for each color. i.e€¦€¦€¦..

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

If you can do with just numbers (that is, without the "st", "nd", "rd" and
color name designations), put this formula in B1 and copy down (change the
999 so that it is larger than the largest row you expect to fill down to).

=COUNTIF($A$1:$A1,A1)

Alright, if the OP wants the ordinal suffixes and color, then here is my
attempt at a formula....

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(COUNTIF($A$1:$A1,A1)&"th
"&A1,"1th","1st"),"2th","2nd"),"3th","3rd"),"11st" ,"11th"),"12nd","12th"),"13rd","13th")

It comes in midway (in length) between the other two submitted formulas.

Rick

Color = the text string red, white, etc.

Colour <== Color .... Notice the "u"... I think the comment was a British
"thing".

{OT: I responded to your comment in the "m.p.e.misc" thread}

Rick

{OT: I responded to your comment in the "m.p.e.misc" thread}

Yesh, I saw it. I'm still playing with that one. You're starting to get over
my head VBA-wise (not one of my strengths).

It would be nice if the OP reponded so we would know what direction to go
with that.

Biff

"Rick Rothstein (MVP - VB)" wrote in
message ...
Color = the text string red, white, etc.

Colour <== Color .... Notice the "u"... I think the comment was a British
"thing".

{OT: I responded to your comment in the "m.p.e.misc" thread}

Rick

{OT: I responded to your comment in the "m.p.e.misc" thread}

Yesh, I saw it. I'm still playing with that one. You're starting to get
over my head VBA-wise (not one of my strengths).

I'd be more than happy to answer any question you may have about that code,
so don't hestiate to ask (over in the other thread, of course<g).

It would be nice if the OP reponded so we would know what
direction to go with that.

That was always a frustration over in the compiled VB newsgroups (where I
have volunteered for the last 6 or 7 years)... nebulous questions asked, one
or more answers provided, silence from the OP. Being that it is happening
here, I guess this is a universal newsgroup affliction.

Rick

I posted a color (colour) issue later:
try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) _
Is Nothing Then Exit Sub
Select Case Target.Value
Case "A" To "E"
icolor = 3
Case "F" To "J"
icolor = 41
Case "K" To "O"
icolor = 4
Case "P" To "T"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
End Sub


"JVANWORTH" wrote:

I need a cell to change into four (4) different colors if a specific
condition is met. For example if A1 matches a text value €œA thru E€� I need
€œred€�, if its a €œF thru J€� then €œblue€�, €œK thru O€� then €œgreen€�, €œP thru T€�
then yellow.

Im struggling with the set up of this requirement.



"Rick Rothstein (MVP - VB)" wrote:

I need to number random conditions as they occur in a column. The
example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first €œred€� selected will be €œRed-1, next €œred€� selected will be
€œRed-2€�. This will happen to for each color. i.e€¦€¦€¦..

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

If you can do with just numbers (that is, without the "st", "nd", "rd" and
color name designations), put this formula in B1 and copy down (change the
999 so that it is larger than the largest row you expect to fill down to).

=COUNTIF($A$1:$A1,A1)

Alright, if the OP wants the ordinal suffixes and color, then here is my
attempt at a formula....

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(COUNTIF($A$1:$A1,A1)&"th
"&A1,"1th","1st"),"2th","2nd"),"3th","3rd"),"11st" ,"11th"),"12nd","12th"),"13rd","13th")

It comes in midway (in length) between the other two submitted formulas.

Rick

Thanks Rick.
--
T in Vegas


"Rick Rothstein (MVP - VB)" wrote:

I need to number random conditions as they occur in a column. The example
show is in its simplest form.

Column A is a list of colors that are selected randomly. Column B needs
to
be the order in which the color was selected relative to equivalent
colors.
So the first €œred€� selected will be €œRed-1, next €œred€� selected will be
€œRed-2€�. This will happen to for each color. i.e€¦€¦€¦..

A B
1 red 1 1st red
2 blue 1 1st blue
3 red 2 2nd red
4 red 3 3rd red
5 white 1 1st white
6 blue 2 2nd blue

When the order changes I want the spread to adjust accordingly.

If you can do with just numbers (that is, without the "st", "nd", "rd" and
color name designations), put this formula in B1 and copy down (change the
999 so that it is larger than the largest row you expect to fill down to).

=COUNTIF($A$1:$A1,A1)

Rick