Giving Cells Sequential Reference Values

[cwhaley1]

Hi all.

In my mind, I imagine a table with two columns... column A with a list of names, column B with a unique reference to identify how many times that name has appeared. Rather than summing the total number of times the name has appeared, I'd like a running total rather like the below:

NAME REFERENCE

Dave 1
Dave 2
Dave 3
Alan 1
Alan 2
John 1
Simon 1
Simon 2

I've tried with COUNTIF but was only able to get a running total rather than the sequential list as above. Each name will only appear in a group as above and would only be entered once (in the real data, the name is a unique project number).

Can anybody help with the right formula I should be using?

 

[mikerickson]

Perhaps something like

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

 

[navic]

(in the real data, the name is a unique project number).

Try

in B1

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

in F1

=COUNTIF($A$1:$A$8,E1)

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[TD="class: xl63, width: 64"]D[/TD]
[TD="class: xl63, width: 64"]E[/TD]
[TD="class: xl63, width: 64"]F[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]Dave[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Dave[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]Dave[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Alan[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl63"]Dave[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]John[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl63"]Alan[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Simon[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl63"]Alan[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]6[/TD]
[TD="class: xl63"]John[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]7[/TD]
[TD="class: xl63"]Simon[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]8[/TD]
[TD="class: xl63"]Simon[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[cwhaley1]

Thanks both for posting this. I used mikerickson's suggestion as he very kindly posted that suggestion first. It works well for what I need it for and has helped me in sorting my data.