Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a .csv open in Excel 2007 that looks like the following

1 566
1 566
1 566
2 341
2 341
3 566
3 566
3 566
3 566
...

There is a one-to-one relationship between Column A and B, but B is not unique (but can only take a small set of valid values). I wish to query how many id's (Column A) contain a particular property (Column B). If the example above ended before the "...", I would like to get as the output:

566 2
341 1

because the property 566 is owned by two id's (1 and 3) and the property 341 is only owned by the id 2.

I think this would be much easier on a relational database, but I have to use Excel.

share|improve this question
    
If you are able to modify the original data set you can use remove duplicates to get rid of the repeating entries then use countif to count the uniques –  Pynner Jan 27 '13 at 16:49
add comment

1 Answer

up vote 0 down vote accepted

If your data is in A2:B100 then try this "array formula" in E2

=SUM(IF(FREQUENCY(IF(B$2:B$100=D2,IF(A$2:A$100<>"",MATCH(A$2:A$100,A$2:A$100,0))),ROW(B$2:B$100)-ROW(B$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

where D2 contains a specific property like 566

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.