Assign a number each letter on list compounding occurence @ each e

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nat Maxwell - 27 Feb 2007 19:50 GMT

hi all,
What should be the syntax if I have a list of first letters of my entries
(looking something like
A
A
B
C
C
C
C
E
J
P
P
P
S
S
T
V

and I wanted to programmatically assign a number to each entry on this list,
restaring the numbering at each letter (e.g., first A would appear as A1,
second as A2, first C as C1, fifth C as C5, etc.)? I know how to compile the
resulting letter with its corresponding numeral, I'm just not sure how/if to
use the MATCH function to count the compounding occurence of each letter on
the list.
Thank you in advance!

Reply to this Message

Ron Rosenfeld - 27 Feb 2007 20:34 GMT

>hi all,
>What should be the syntax if I have a list of first letters of my entries
[quoted text clipped - 23 lines]
>the list.
>Thank you in advance!

If your first entry is in cell A1, then:

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

and copy/drag down as far as required.

This should give you the count you are looking for
--ron

Reply to this Message

Gary''s Student - 27 Feb 2007 20:35 GMT

In B1 enter:
1
In B2 enter:
=IF(A2=A1,B1+1,1)
and copy down

Signature

Gary''s Student
gsnu200708

> hi all,
> What should be the syntax if I have a list of first letters of my entries
[quoted text clipped - 23 lines]
> the list.
> Thank you in advance!

Reply to this Message

Teethless mama - 27 Feb 2007 20:41 GMT

Assuming your data in Sheet1
Create a name range. Example: ALPHA

In Sheet2
In A1:
=IF(ISERR(SMALL(IF(ALPHA="A",ROW(INDIRECT("1:"&ROWS(ALPHA)))),ROWS($1:1))),"",INDEX(ALPHA,SMALL(IF(ALPHA="A",ROW(INDIRECT("1:"&ROWS(ALPHA)))),ROWS($1:1))))

Copy across, all you have to change a letter "A" on the second column to
"B" and so on...
then copy down

> hi all,
> What should be the syntax if I have a list of first letters of my entries
[quoted text clipped - 23 lines]
> the list.
> Thank you in advance!

Reply to this Message

Nat Maxwell - 27 Feb 2007 21:05 GMT

Thank you all very much! Will try out all three methods.
Have a wonderful day!

> Assuming your data in Sheet1
> Create a name range. Example: ALPHA
[quoted text clipped - 34 lines]
> > the list.
> > Thank you in advance!

Reply to this Message