Frequency of values in a huge asymmetrical range/ table

[sauronbaggins]

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.

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	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. I do not have the list of all the unique "sport" values. The unique values run in a few thousands, so it is not possible to do it manually. I first need to create a list of unique values and then find the frequency of each of those across the range.

 

[Eric W]

I'm not sure what you want. Is this the same as your original question, but if there are more than 4 items on a line, ignore the "middle" items?

 

[sauronbaggins]

I'm not sure what you want. Is this the same as your original question, but if there are more than 4 items on a line, ignore the "middle" items?

The solution you provided earlier worked for the first analysis. Now we want to look at the 1st and last 2 author frequencies. This is like an additional analysis of the same data.

" but if there are more than 4 items on a line, ignore the "middle" items? "

It is not that straightforward actually. The number of comma separated values ranges from 1 to a large number, i.e., it is not same and some rows might only have 1 or 2 or 3 or 4 values. I tried a number of solutions I got from google, but none work. I'm sure the answer lies in some nested formula to look and extract for the last 2 values using from right, but nested formulas get too confusing for me sometimes.

 

[Eric W]

If your data looks like your first post, this is too complicated for formulas. But again, I still don't understand what you want. If you can show some sample date, with some sample expected results, that would help. My current best guess is to do the same thing as the original macro, but but include a condition so that it only examines the last 2 non-blank items on each line.