Assigning a value based on number of appearances

[Datalipps]

Example: I'm trying to assign a value based on the order a field appears in a column. If my original data set is column A, my desired result would be column 'B'. I think I need a CSE formula. Thanks in advance.

Column A - Column B
Dan - 1
Dan - 2
Dan - 3
Earl - 1
Earl - 2
Jim - 1
Matt - 1
Matt - 2
Matt - 3
Kyle - 1
Kyle - 2

 

[T. Valko]

Example: I'm trying to assign a value based on the order a field appears in a column. If my original data set is column A, my desired result would be column 'B'. I think I need a CSE formula. Thanks in advance.

Column A - Column B
Dan - 1
Dan - 2
Dan - 3
Earl - 1
Earl - 2
Jim - 1
Matt - 1
Matt - 2
Matt - 3
Kyle - 1
Kyle - 2

Try this...

Book1
	A	B
2	Dan	1
3	Dan	2
4	Dan	3
5	Earl	1
6	Earl	2
7	Jim	1
8	Matt	1
9	Matt	2
10	Matt	3
11	Kyle	1
12	Kyle	2
Sheet1

This formula entered in B2 and copied down:

=COUNTIF(A$2:A2,A2)

 

[RSXchin]

that's genius. I never thought about NOT hardcoding the row... Now I feel silly.

 

[T. Valko]

that's genius. I never thought about NOT hardcoding the row... Now I feel silly.

Using that method is OK as long as you don't have 10's of 1000's of rows of data.

For very large data sets you could do this...

Enter 1 in B2.

Enter this formula in B3 and copy down to the end of data:

=IF(A3=A2,B2+1,1)

 

[RSXchin]

that's only useful if the data is sorted.

 

[T. Valko]

that's only useful if the data is sorted.

Yes

 

[Datalipps]

Yes, the data will be sorted. It would be interesting to know how to do that w/o sorting the data and having it referencing a date field but I'll tackle that on the next revision.

Thanks for all of the help, I'm embarrassed it was that easy!

 

[T. Valko]

Yes, the data will be sorted. It would be interesting to know how to do that w/o sorting the data and having it referencing a date field but I'll tackle that on the next revision.

Thanks for all of the help, I'm embarrassed it was that easy!

You're welcome. Thanks for the feedback!