Here’s a simple two column dataset
| Comment Identifier | Intervals |
| A | 3pm-6pm |
| A | 9pm-12pm |
| S | 3pm-6pm |
| S | 3pm-6pm |
| S | 9pm-12pm |
| A | 9pm-12pm |
| S | 9pm-12pm |
| D | 3pm-6pm |
| A | 9pm-12pm |
| A | 9pm-12pm |
| A | 9pm-12pm |
| A | 3pm-6pm |
| A | 3pm-6pm |
For identifiers listed in column A, there are time intervals in column B. Note that for a certain identifier, a time interval can appear multiple times. The objective is two-fold:
- For each identifier, show the time interval which appears most frequently; and
- For each identifier, compute the count of the time interval which appears most frequently
The expected result is:
As you can observe, the result for S is two time periods – 3pm-6pm and 9pm-12pm. This is because each of them appears twice.
You may download my solution from here.
In a Pivot Table, show the most frequently appearing text entry by a certain parameter
{ 0 Comments }