Functions expert………numbering random condition in a column

[JVANWORTH]

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.

 

[Bob Phillips]

=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)

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

Colour?

--
---
HTH

Bob

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

 

[JVANWORTH]

just brilliant

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]

just brilliant

=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)

 

[T. Valko]

???

Biff

Colour?

--
---
HTH

Bob

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

 

[Bob Phillips]

<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)

 

[T. Valko]

Color = the text string red, white, etc.

Biff

<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)

 

[Rick Rothstein \(MVP - VB\)]

I need to number random conditions as they occur in a column. The

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(SUBSTITUTE(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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

{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\)]

{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,

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

 

[JVANWORTH]

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

 

[Tom]

Thanks Rick.
--
T in Vegas

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