munch007
asked on
Number Unique Values Concatenated
I have the following data and want to ount up the number of unique thk + rad combinations. I should end up with 4 in this example. How can I do this?
thk rad1 rad2 rad3 rad4 rad5 rad6
0.118 0.125
0.118 0.18 0.136 0.125
0.118 0.125
0.394 0.125
If they were not formatted as shown above, we would have this data:
.118 .125
.118 .18
.118 .136
.118 .125
.118 .125
.394 .125
I get 4 unique values here
unique.xls
thk rad1 rad2 rad3 rad4 rad5 rad6
0.118 0.125
0.118 0.18 0.136 0.125
0.118 0.125
0.394 0.125
If they were not formatted as shown above, we would have this data:
.118 .125
.118 .18
.118 .136
.118 .125
.118 .125
.394 .125
I get 4 unique values here
unique.xls
ASKER
I cannot apply a filter to do this. Not an option.
The functionality of excel when it comes to getting distinct values is relatively limited, out of the box. You could write a short macro for this if you're up to it, unfortunately I'm too busy today. Maybe someone else will write you one. Good luck.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then, use the subtotal function in your summation box. The first argument, 3, lets the subtotal function know that it is giving a "CountA" of all the visible records (database term for row).
See attached
unique.xlsx