sauronbaggins
New Member
Hi guys,
I have tried googling and the things that I know. However, I am not getting the solution. I have an asymmetrical data set of names. It has approx 20000 rows. Column A is never empty for any of the rows. The farthest column that holds a value in it is "HZ" (not all rows have values until HZ, meaning a LOT of the columns are empty. I made it a "Table" and tried to summarize with a pivot table but that doesn't work. What's the best way to find frequency of all the names in such a huge range?
To give you an idea of how the data looks, please see this table.
The output I want is this:
If there is a way to consolidate/ transpose all the values across this range in a single column, that might also work.
I have tried googling and the things that I know. However, I am not getting the solution. I have an asymmetrical data set of names. It has approx 20000 rows. Column A is never empty for any of the rows. The farthest column that holds a value in it is "HZ" (not all rows have values until HZ, meaning a LOT of the columns are empty. I made it a "Table" and tried to summarize with a pivot table but that doesn't work. What's the best way to find frequency of all the names in such a huge range?
To give you an idea of how the data looks, please see this table.
Game | Column 1 | Column 2 | Column 3 |
---|---|---|---|
Tennis | Badminton | Chess | |
Golf | | ||
Tennis | Baseball | ||
Basketball | Table Tennis | ||
Tennis | Golf | ||
Hockey | |
The output I want is this:
Game <Heading doesn't really matter; alphabetically ordered doesn't matter, I can sort them later> | Count |
Tennis | 3 |
Golf | 2 |
Hockey | 1 |
Badminton | 1 |
Baseball | 1 |
Basketball | 1 |
Chess | 1 |
Table Tennis | 1 |
If there is a way to consolidate/ transpose all the values across this range in a single column, that might also work.