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

Extract a Range of Data into a Unique List

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.