Challenge #11: Identify Logical Groups

[nini]

My Solution:

[kmcdaniel]

Spoiler

[ddiesel]

My solution:

Spoiler

[KT2]

Spoiler

Boom.

[Sebenius]

intresting chaning on the input but my old solution did the trick still:

Spoiler

[erick_irigoyen]

Solution attached

[SueDonim]

I had downloaded the data before it all changed, so was working with the larger dataset.

I inadvertently plugged the search values directly in the formulas, rather than link to it.  I might redo the exercise to deal with that, but this seems pretty good ATM

Only issue is a discrepancy of 55 trigger complaints (my number is lower), but all the others match.  I'm guessing this is an issue with the truncated fields which is probably why the challenge was revised.

Spoiler

Process:
- Replace the complaint row separator of "." with "|" so it's easier to work with
- Add Record ID to allow for grouping
- Revise Record ID with Multi-Row tool such that it only increments if there is a pipe separator
- Flag instances of search term for all 7 permutations of the search term
- Aggregate with Group By Record ID and count all instances of each permutation
- Sum each of the 7 permutations
- Remove double-counting
- Transpose data to get it in columns
- Sort (descending) and Clean-up

[sgrabish1]

Easy to do with guide alongside; thanks for the practice

[KatieA]

Solution attached