running count if total

[Loss1003]

I’m trying to create a formula in Column C to produce a running count of duplicate entries in column B.

As more account names are added to column B I’d like the running total of duplicates to add (+1) in Column C from the previous duplicate name in column A found. So every ttime a duplicate name is found column c would increase the count by one

Column B Column C

Intercontinental Marble & Granite Corp.

1

Pentagon Properties, LLC.

1

SAXX Mobile

1

Consulting RE, LLC Lake Placid Rail LLC

1

JC Corp

1

Steelcad Industrial Services

1

St. Charlest Properties

1

Xtra Airways

1

New Hawthorne Valley

1

New Hawthorne Valley

2

Consulting RE, LLC Lake Placid Rail LLC

2

New Hawthorne Valley

3

Thanks,

[austenr]

Untested but, put this in the first row of C or where ever your data starts and copy down: You can adjust the number of rows from 100 to whatever suits your needs.

[vba]=IF(COUNTIF(A$1:A1,"="&A1)>1,"",COUNTIF(A$1:A$100,"="&A1)) [/vba]

[Loss1003]

Steelcad Industrial Services 1
St. Charlest Properties 1
Xtra Airways 1
New Hawthorne Valley 5
New Hawthorne Valley
New Hawthorne Valley
New Hawthorne Valley
New Hawthorne Valley

Thanks. Can we adjust the formula to have a running count of each record of duplicate entry. in the above using the formula produces a count of 5 in the first record for New Hawthrone Valley. I'd like the count to start at 1, then 2, 3, 4 and so on.

Much Appreciated!

[austenr]

Why would you want to do that? So if I understand you, you want

New Hawthorne Valley 1
New Hawthorne Valley 2, etc.?

With the entries in no particular order (grouped, sorted) that would be a little tedious, plus you would want to make it dynamic (the formula that is)since you don't know how many rows you are going to have. May I suggest a macro that sorts then would number them individually. Someone else might have an idea but dont have time to throw something together right at the moment.

[austenr]

If you want to sort your data in the next column over try:

[VBA]=IF(A2=A1,"",MAX($B$1:B1)+1) [/VBA]

and drag down. Again untested.

[hardlife]

=if(countif(a$1:a1,"="&a1)=1,1,countif(a$1:a1,"="&a1))

happy winter day