How to find the names of participants in this MS Excel sheet?
January 11, 2019 1:45 PM   Subscribe

How to find the names of participants in this MS Excel sheet?

I have a large Excel sheet which keeps track of the participants in a recurring office event, about 50 names listed in one column, only 7-10 of which participate on any given date, and the dates they participated in subsequent columns. I need to create a way to organize this list by the last date someone participated, and also dynamically populate a list of who participated on what dates in the past and going forward. I have the first part down, at least I created a column that shows the last participation date, but I can't figure out how to create a list or table of the past participants on a given date using only the columns of names and dates, and have that able to extend to future occurrences. Sure there's a way to do this with a fancy table trick?
posted by T.D. Strange to Work & Money (4 answers total)
 
This is what I would do:
I understand your list looks something like my columns A through K
  • For the Last participation date I used =MAX() to calculate the highest number out of the dates each person participated.
  • To calculate the assistants to each event:
    1. On column P I entered all the event dates
    2. On cell R1 I pasted the participants names using Paste Special Values -> Transpose.
    3. The heavy lifting is in the formula filling the range between R2 and the last cell from the range that generated from the last two steps (in my case AQ104) I'll try to explain it from the inside out:
      • The MATCH gives us the row where the current participant's assitances are registered (in the case of participant A it is 2)
      • ADDRESS is used to create the cell name for the first and final cell for events in the row we calculated above (B2 and K2 respectively)
      • INDIRECT is used to create the range (B2:K2) programatically instead of entering the string.
      • If the event date on the column P appears on that range at least once (COUNTIF), it enters the participant's name on this cell
    4. Finally on Column Q i join all the names that appeared on that date's event, ignoring blanks


posted by fjom at 2:58 PM on January 11


Thanks, I'll work with that some. This is basically what I have: link
posted by T.D. Strange at 3:05 PM on January 11


I can think of a couple of table tricks that may not be as elegant as a formula, but may get you where you want to be. If you select the range of data and Insert > Table > Convert to Table, you can use the drop downs at the header of a column to filter so that only rows where that date is filled in are displayed. You can copy and paste the names elsewhere, clear the filter, and go on to the next date.

Alternately, a pivot table will do this easily. Select your range of data (I think you may want to not select row A so that the dates will act as the header. Also, add a header like “Name” to column 1) and go to Insert > Pivot Table. You can choose to put it somewhere in that worksheet or on a fresh worksheet.

Select the blank pivot table and right click to bring up your field options menu. Drag one of the dates to the “Field” category. Drag “Name” to the “Rows” category.

In the pivot table click on the drop down next to the date filter, which says “All” by default. You should see options to filter for a blank (didn’t participate) or the date (did participate). If you select the date, you should get a list of the participants below.

You could either create a pivot table for each date or you can copy and paste the names somewhere else in the workbook and keep reusing that pivot table for all the dates.

This is much easier to do than to describe, I promise! If you have a lot of dates it may be more practical to work out the formula, but if it’s maybe 10 or fewer, Table tricks may be faster.
posted by Kriesa at 7:48 PM on January 11


Going forward, you could keep your data in a way that will make pivot tables do exactly what you want:
Column A: Event number
Column B: Date
Column C: Name

Then you can use a Pivot Table where Name is the field in the row category and Date is the field in the column category to produce a table in basically your current format that will stay updated.
posted by Kriesa at 7:57 PM on January 11


« Older Investment vehicle or company for college student?   |   Is there such a thing as a medium-intelligence... Newer »

You are not logged in, either login or create an account to post comments