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:
- At the bottom of the first column, add a cell which references the cell at the top of the second column. Use relative addressing.
- Fill-Left this new cell all the way across to the second last column.
- Fill-Down this new row to r*c extra rows (in my case it is 38 hosps by 47 answers, which is a lot.
- The first column, stretching down and down and down, contains every cell in the array in a single long column.
- Copy and paste ‘values’. You can now delete the remaining 46 columns.
- 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.
- Then sort/remove duplicates/etc to your heart’s content.