Hello,
This first table is my original data which contains several columns with binary data 'Yes' or/and 'No'.
Col1 | Col2 | Col3 |
Yes | Yes | No |
Yes | Yes | No |
No | Yes | No |
No | Yes | No |
Yes | Yes | No |
Yes | Yes | No |
Yes | Yes | No |
Yes | Yes | No |
No | Yes | No |
Yes | Yes | No |
The second one is the output table that I need.
Yes | No | Total | Score | |
Col1 | 7 | 3 | 10 | 0.7 |
Col2 | 10 | 0 | 10 | 1 |
Col3 | 0 | 10 | 10 | 0 |
I tried Summarize tool to get the count of 'Yes' and 'No' for each column. However, if I have 20 columns, then 20 summarized tools need to be used which I think is too complex.
Please help me if you know a better way. Any help is appreciated!
Solved! Go to Solution.
Hey @s100 ,
One way would be to transpose your data so you only need a single summarize tool to count the Yes/Nos per column.
Then you only need a formula tool to find the score.
Hope that helps,
Angelos
@s100 A neater solution, use a Frequency Table tool from the Data Investigation tool palette.
Same results more or less, but only a single tool used and it will automatically return the Score value
Hi @s100
Here is how you can do it.
Workflow:
1. Using transpose tool converting column name to rows.
2. Using formula tool create count column.
3. Using crosstab converting rows to table with column with yes, no & total.
4. Using formula tool calculating score.
Hope this helps 🙂
@AngelosPachis @atcodedog05 Thank you so much for your help! These solutions work.