I’ve just worked out this poisonous little trick and did not find anything about it on the internet, so here it is:

Problem:

Hospitals in a survey have between zero and 47 ‘areas of work’ which psychologists undertake. I have 199. That multiplied by the 47 is too many to do by hand. I have a list for each hospital. I want a single list which I can then sort, remove duplicates, etc.

Solution:

1. At the bottom of the first column, add a cell which references the cell at the top of the second column. Use relative addressing.
2. Fill-Left this new cell all the way across to the second last column.
3. Fill-Down this new row to r*c extra rows (in my case it is 38 hosps by 47 answers, which is a lot.
4. The first column, stretching down and down and down, contains every cell in the array in a single long column.
5. Copy and paste ‘values’. You can now delete the remaining 46 columns.
6. As a check, you can count the non-missings of the array, then count the non-missings in the now-extended 1st column. If they are different, then you have a problem somewhere.
7. Then sort/remove duplicates/etc to your heart’s content.