Turning a 2D spreadsheet array into a single long list.

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


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.


  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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s