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

[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.

[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, Copy the sheet (select the whole sheet by clicking the 'Data' - the cell above row 1 and to the left of column A) to a new sheet (with cell A1 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 data 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 row 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 G3 together, then formula-drag the small box in the bottom right corner of that selector to the last row with data.

[rkhuntjr]

Awesome.

You da man.