### Extract a Range of Data into a Unique List

Not often used since I typically do not work with multiple columns of related data (i.e., multiple columns of Sales figures rather than Sales figures listed down an entire column), but I do come across it on occasion and thought this tip is rather handy.

It saves the step of having to copy and paste the data into a single column which always runs the risk of leaving data out.

(Note: I’m using words in the example below so it’s easier to follow the output.) In this example, the formula in G1 is:

=INDEX(\$A\$1:\$D\$4,1+INT((ROW(A1)-1)/COLUMNS(\$A\$1:\$D\$4)),MOD(ROW(A1)-1+COLUMNS(\$A\$1:\$D\$4),COLUMNS(\$A\$1:\$D\$4))+1)

A Named Range would be useful to replace the “\$A\$1:\$D\$4” reference if it’s easier to follow.

I kept the “#REF!” output so you can see that the result is when you copy the formula down more rows than is needed.  In this case, because the range “\$A\$1:\$D\$4” is a 4 x 4 grid, copying the formula down more than 16 rows (4*4=16) will result in the “#REF!” result.