Reputation: 437
I have event tracking data in Excel that is a bit like this.
Event | Name | Attend 1 | Attend 2 | Attend 3 | |
---|---|---|---|---|---|
Event 1 | Joe | Joe@email | Yes | ||
Event 2 | Joe | Joe@email | No | ||
Event 1 | Bob | Bob@email | No | ||
Event 1 | Sara | Sara@email | Yes | ||
Event 2 | Sara | Sara@email | Yes | ||
Event 3 | Sara | Sara@email | Yes | ||
Event 2 | Ray | Ray@email | Yes | ||
Event 3 | Ray | Ray@email | No |
I am trying to combine / collapse the data so that each Name & Email is a unique line (using email as the unique identifier) and merge the Attend row data. The blank cells are also important for later work (person did not register for the event). The end data should look like this:
Name | Attend 1 | Attend 2 | Attend 3 | |
---|---|---|---|---|
Joe | Joe@email | Yes | No | |
Bob | Bob@email | No | ||
Sara | Sara@email | Yes | Yes | Yes |
Ray | Ray@email | Yes | No |
I've tried things like adding helper columns to ID duplicate rows, filtering, and various lookup versions, but I keep getting stuck. Most solutions I find online showed concatenating data, using groups, or pivot tables that don't get the end result I need or used commercial add-ons like AbleBits. I saw a similar sounding post on SO (link) but it didn't quite get at my situation and the solution didn't seem to work for me (unless I was not doing it right). Is there a way to use Excel formulas or regular menu options to obtain my result? If there are more complex solutions, e.g. VBA or Power Query, please provide detailed steps as I'm not familiar with those tools. I could likely export the data and resolve it in R, but I'm looking for a native Excel solution.
Thanks!
Upvotes: 0
Views: 1089
Reputation: 2195
You can do =UNIQUE(C1:C100)
in a separate column and copy and paste those values over themselves (assuming the range of emails is in C2:C100 and that the headers are in the first row).
Then in an adjacent column do something like =CONCAT(FILTER(D$2:D$100, $C$2:$C$100=$H2))
and drag down and to the right (where column H contains the column of unique emails). Then simply copy and paste values over themselves again and remove the old columns.
Upvotes: 1