Count unique cells and display them in column

Question

I am building a list of gigs I attended and I want to count how many times I've seen each band.

I know about UNIQUE, but because I keep each band in separate column it just copies each row.

Given the table (or screenshot of real data):

|   Date   | Venue  |                   Bands                    |
|----------|--------|--------|--------|--------|--------|--------|
| 02.02.17 | Venue1 | Band A | Band B | Band C | Band D | Band E |
| 02.07.17 | Venue3 | Band D | Band C |        |        |        |

The output I want:

|  Band  | Attended |
|        | (times)  |
|--------|----------|
| Band A |        1 |
| Band B |        1 |
| Band C |        2 |
| Band D |        2 |
| Band E |        1 |

I can change structure if needed.

What happens after using UNIQUE: https://i.sstatic.net/qmszk.png

Thanks in advance.

Answer 1

Step 1. Get list of all unique bands in one column, one per row =ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(Gigs!D2:Z&CHAR(9)); CHAR(9)))))

Step 2. Place this formula in next column, and drag it down

=SUM(COUNTIF(Gigs!D:Z; E2))

Answer 2

Transform your data to a simple table format in order to make easier to do data-analysis.

A simple table use the first row for column headers a.k.a. fields and has one and only one column for each entity, let say only one column for band names.

The above could be done in a single but complex formula hard to debug, so it's better to start by doing this using simple formulas and once you are certain that all is working fine, think about making a complex formula or writing and script.

Related

• Unpivot Matrix to Tabular. Using counts of two variables into individual rows
• Generate a list of all unique values of a multi-column range and give the values a rating according to how many times they appear in the last X cols
• Normalize (reformat) cross-tab data for Tableau without using Excel
• How do you create a "reverse pivot" in Google Sheets?