EXCEL - table to list for mail merge

Question

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

Could you include your data sample as Markdown table ? I feel i've seen similar questions before; what are your options? formula based, VBA, combined? Which Excel version are you using? — Excellor
– Excellor, Commented Sep 10, 2024 at 13:54
@Excellor It's pretty much just UnPivoting the data, and then Filtering to exclude blanks — Chronocidal
– Chronocidal, Commented Sep 10, 2024 at 14:30

Chronocidal · Accepted Answer · 2024-09-10 14:55:52Z

0

=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)

answered Sep 10, 2024 at 14:55

Chronocidal

8,4101 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXCEL - table to list for mail merge

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related