Excel 2013 Counting Rows per record

All,
I am working with a spreadsheet with ~60,000 rows.  Each row has data pertaining to one column (Column A).  I want to count the number of times a value in Column B is associated with a unique value in Column A.  And a value in Column B can appear multiple times per unique value in Column A; we only want to a Column B value one time per unique Column A value.

For instance:
Column A     Column B
  New York      Bob
  New York      Sue
  New York      Steve
  New York      Bob
  Chicago        Mary
  Chicago        Mary
  Chicago        Mary

In Column C, we like to have the # of unique values of column B associated with each unique value of column A, so the result would look like this:

Column A     Column B   Column C
  New York      Bob               3
  New York      Sue                
  New York      Steve
  New York      Bob
  New York      Sue
  Chicago        Mary              1
  Chicago        Mary
  Chicago        Mary

Can this be done in Excel?  And if it can, can someone tell us how?
Thanks!
NPRGadminAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NBVCConnect With a Mentor Commented:
Since you have around 60,000 rows, perhaps using a helper column might improve efficiency over using array formulas....

Assuming your data is in columns A:B, then in C2 enter formula:

=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

copied down.  (you can hide this column, if desired)

in D2 then:

=IF(A2=A1,"",COUNTIFS(A:A,A2,C:C,1))

copied down.
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try this array formula

'=IF(A2=A1,"",SUM(IF(FREQUENCY(IF($A$2:$A$10=A2,MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($A$2:$A$10)-ROW(A2)+1),1)))

Open in new window


array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER

Regards
ArrayFormula.xlsx
0
All Courses

From novice to tech pro — start learning today.