PDA

View Full Version : running count if total



Loss1003
12-01-2010, 10:22 AM
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
12-01-2010, 10:47 AM
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.

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

Loss1003
12-01-2010, 11:39 AM
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
12-01-2010, 01:30 PM
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
12-01-2010, 01:57 PM
If you want to sort your data in the next column over try:

=IF(A2=A1,"",MAX($B$1:B1)+1)

and drag down. Again untested.

hardlife
12-03-2010, 03:50 PM
=if(countif(a$1:a1,"="&a1)=1,1,countif(a$1:a1,"="&a1))

happy winter day :hi: