running count if total

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

Quote:

---

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,

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]

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!

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.

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.

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

happy winter day :hi: