Number of unique attributes that multiple people have

[rkhuntjr]

Hello all. This is kinda hard to explain, so you might want to just
check out the attached spreadsheet to see what I need.

I need Excel to tell me the number of unique attributes that multiple
people (each person having multiple entries with multiple attributes)
have.

Is Excel even capable of doing this? My spreadsheet has 7000+ entries,
so I can't really have an improvised solution.

Thank you for your help!
Richard


+-------------------------------------------------------------------+
|Filename: help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3624 |
+-------------------------------------------------------------------+

 

[Bryan Hessey]

There are more clever ways to do what you ask, but I would do this by
simple helper columns ( I can understand simple), copy-paste-special
Values, and sort as shown in the attached.

Then I could adapt such formula to other uses.

Hope this helps.


+-------------------------------------------------------------------+
|Filename: helper.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3625 |
+-------------------------------------------------------------------+

 

[rkhuntjr]

Bryan,
Thanks so much for your help. Your solution was almost perfect. The
numbers came out just right. However, my spreadsheet is huge, so it
would take all day to sort through the numbers for the individual
people (there are thousands of them). Is there any way I can get Excel
to instantly (or quickly with a little manipulation) tell me all I need
to know (like my example output on the bottom of the spreadsheet).

Thanks again
Richard

p.s. I just wanted to clarify the fact that Persons A,B&C won't
necessarily have 10 entries apiece.

 

[Bryan Hessey]

Richard,

After you copy the formula down the columns in the first sheet, Cop
the sheet (select the whole sheet by clicking the 'Data' - the cel
above row 1 and to the left of column A) to a new sheet (with cell A
selected) and paste-special, VALUES, then select the sheet and Data
Sort over the column that is E and second sort over A in my 'Values
sheet, this will give a sheet as per my 'Sorted' sheet with your dat
listed at the front ( the 1 values in column E)


I should have added, that in the first sheet columns D E F and G at ro
2 can be dragged to Row 3, but G3 has a different formula.
When you have the formula set for row 3, select D3 E3 F3 and G
together, then formula-drag the small box in the bottom right corner o
that selector to the last row with data

 

[rkhuntjr]

Awesome.

You da man.