I’m helping a group update a competition score sheet and need help converting a table with gaps to a list.
Before Example
| Number | Name | address | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|---|---|
| 1 | david | xxdavid | apples | potatos | |||
| 2 | helen | xxhelen | pears | ||||
| 3 | caroline | xxcaroline | pears | potatos | carrots | ||
| 4 | jacqui | xxjacqui | carrots | beans | |||
| 5 | Nigel | zznigel | pears | carrots |
After Example
I’m comfortable doing this in R, however this group is less tech savvy (I won’t be able to help them every time), and so I was hoping to find a way to do this purely in excel with as few steps as possible. The after example is to be used for a mail merge which creates the entry cards.
In practice there are many more entrants/categories or I would just do it manually for 5 people!
Grateful for any advice, i’m sure I can scale up to the required table size if someone could suggest a step/function way forward.
EDIT: changed before example to markdown
3
=LET(_people, A1:C6, _items, D1:H6,
_data, HSTACK(CHOOSEROWS(DROP(_people,1),QUOTIENT(SEQUENCE((ROWS(_items)-1)*COLUMNS(_items),,0),COLUMNS(_items))+1),TOCOL(IFNA(EXPAND(INDEX(_items,1,),ROWS(_items)-1),INDEX(_items,1,))),TOCOL(DROP(_items,1))),
VSTACK(HSTACK(INDEX(_people,1,),"Entry Number", "Description"), FILTER(_data,INDEX(_data,,COLUMNS(_people)+2)<>"","")))
This will first unpivot the data (without headers) into the temporary _data array — which creates a row for every entry, even if it is blank.
It then filters the _data array on its last column, to eliminate any rows with blank entries, and then adds the Headers back in.
Updating the ranges for _people (A1:C6) and _items (D1:H6) will automatically update your output, so long as they both have the same number of rows (and include the Headers)