Solved

# Excel 2013 Counting Rows per record

Posted on 2014-02-03
293 Views
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!
0

LVL 48

Assisted Solution

Rgonzo1971 earned 250 total points
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)))
``````

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

Regards
ArrayFormula.xlsx
0

LVL 23

Accepted Solution

NBVC earned 250 total points
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

## Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …