{"id":2808,"date":"2016-01-08T11:53:23","date_gmt":"2016-01-08T11:53:23","guid":{"rendered":"http:\/\/wiki.davelevy.info\/?p=2808"},"modified":"2020-12-23T09:10:09","modified_gmt":"2020-12-23T09:10:09","slug":"omg-excel-arrays-a-uniq-filter","status":"publish","type":"post","link":"https:\/\/davelevy.info\/wiki\/omg-excel-arrays-a-uniq-filter\/","title":{"rendered":"OMG Excel Arrays (a UNIQ filter)"},"content":{"rendered":"<p>Right. I needed to write a <acronym title=\"UNIQ is the UNIX filter that performs this function.\">UNIQ<\/acronym> filter in an excel spreadsheet and this needed to be implemented using functions i.e. not VB for two reasons; I can&#8217;t and the customer doesn&#8217;t want me to.<!--more--><\/p>\n<p>I start with<\/p>\n<p><iframe loading=\"lazy\" title=\"Excel Magic Trick 1023: Extract Unique List of Names For Dynamic Data Validation Dropdown List\" width=\"1099\" height=\"618\" src=\"https:\/\/www.youtube.com\/embed\/3u8VHTvSNE4?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" allowfullscreen><\/iframe><\/p>\n<p>To find this use youtube search with the argument excel magic tricks. We are looking for example 1023. The author, has written a book, which is available from amazon.com here&#8230;<\/p>\n<p>I found the following microsoft references useful.<\/p>\n<ol>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/INDEX-function-a5dcf0dd-996d-40a4-a822-b56b061328bd\">INDEX function at office.com<\/a><\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/ROW-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d\">ROW function at office.com<\/a><\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a\">MATCH function at office.com<\/a><\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/SMALL-function-17da8222-7c82-42b2-961b-14c45384df07\">SMALL function at office.com<\/a><\/li>\n<\/ol>\n<p>This video shows a simple way to calculate the number of uniq values in a list.<\/p>\n<p><iframe loading=\"lazy\" title=\"Excel Magic Trick 698: Extract Unique Items w Formula For Data Validation Drop-Down List\" width=\"1099\" height=\"824\" src=\"https:\/\/www.youtube.com\/embed\/IhuURsu0jdI?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" allowfullscreen><\/iframe><\/p>\n<p>I have built an example,\u00a0 <a href=\"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/01\/Uniq-Filter-demo.xlsx\" rel=\"\">Uniq Filter demo<\/a> in Excel this time.<\/p>\n<p style=\"text-align: center;\">ooOOOoo<\/p>\n<p>Image Credit: @flickr CC Zamzara 2006 BY-NC-ND +<a href=\"https:\/\/www.flickr.com\/photos\/zamzara\/218890918\">here&#8230;<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Right. I needed to write a UNIQ filter in an excel spreadsheet and this needed to be implemented using functions i.e. not VB for two reasons; I can&#8217;t and the customer doesn&#8217;t want me to.<\/p>\n","protected":false},"author":1,"featured_media":2979,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_share_on_mastodon":"0"},"categories":[3],"tags":[989,156,911,990],"class_list":["post-2808","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","tag-array-processing","tag-excel","tag-technology","tag-uniq"],"share_on_mastodon":{"url":"","error":""},"jetpack_featured_media_url":"https:\/\/davelevy.info\/wiki\/wp-content\/uploads\/2016\/01\/uniq-beans-w650.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2808","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/comments?post=2808"}],"version-history":[{"count":1,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2808\/revisions"}],"predecessor-version":[{"id":5441,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/posts\/2808\/revisions\/5441"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media\/2979"}],"wp:attachment":[{"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/media?parent=2808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/categories?post=2808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/davelevy.info\/wiki\/wp-json\/wp\/v2\/tags?post=2808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}