Google Spreadsheet How do I extract all unique integers from a multidimensional array?

Question:

( by Bradlaha )

How would I extract all unique integers from a multidimensional array of mixed data types?  For example, if I have:

1/1/2013              2            sometext                3          1

1/2/2013              5            somemoretext         2

1/3/2013           100            even more text        5          6

The output would be:

1

2

3

5

6

100

========

Update:

The data in the example above is the result of a FILTER(range, criteria) function call, so I cannot access columns directly, i.e. B:B, D:D, and E:E, since those ranges take on different names from the original range passed into FILTER.

Solution:

Have a look at the following screenshots:

Sheet1:

I have the following formula in Cell G1:

=sort(unique(transpose(split(concatenate(arrayformula(iferror(regexextract(concat("#";A:E);"^[#][0-9]+$"))));"#"))))

NOTE: You can replace "A:E" with any range (that can be a result of filter).


Sheet2:

I have the following formula in Cell A1:

=sort(unique(transpose(split(concatenate(arrayformula(iferror(regexextract(concat("#";filter('Sheet1'!A:E;isnumber('Sheet1'!E:E)));"^[#][0-9]+$"))));"#"))))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive