{"id":8987,"date":"2022-09-16T15:32:13","date_gmt":"2022-09-16T10:02:13","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/"},"modified":"2022-09-16T15:32:13","modified_gmt":"2022-09-16T10:02:13","slug":"solved-formula-for-unmatched-row-cell-and-display-value-in-one-column","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/","title":{"rendered":"[Solved] Formula for unmatched row cell and display value in one column"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function<\/a>\u00b9 and the updated CONCAT function<\/a>\u00b2 have difficulty with conditional concatenation beyond TEXTJOIN’s blank\/no-blank parameter.<\/p>\n

Here are a pair of User Defined Functions (aka UDF<\/strong>) that will perform the tasks.<\/p>\n

Function udfUniqueList(rng As Range, _\n        Optional delim As String = \",\")\n    Dim str As String, r As Range\n\n    'always truncate ranges as parameters to the usedrange\n    Set rng = Intersect(rng, rng.Parent.UsedRange)\n\n    str = rng(1).Value2\n    For Each r In rng\n        If Not CBool(InStr(1, delim & str & delim, delim & r.Value2 & delim, vbTextCompare)) Then\n            str = str & delim & r.Value2\n        End If\n    Next r\n\n    udfUniqueList = str\n\nEnd Function\n\nFunction udfRogueHeaders(rng As Range, hdr As Range, _\n        Optional delim As String = \",\", _\n        Optional bBlnks As Boolean = False)\n    Dim i As Long, bas As String, str As String\n\n    'always truncate ranges as parameters to the usedrange\n    Set rng = Intersect(rng, rng.Parent.UsedRange)\n\n    'reshape hdr to be identical to rng\n    Set hdr = hdr.Resize(rng.Rows.Count, rng.Columns.Count)\n\n    bas = rng(1).Value2\n    For i = 1 To rng.Cells.Count\n        If (CBool(Len(UCase(rng.Cells(i).Value2))) And Not bBlnks) Or _\n          bBlnks Then\n            If UCase(bas) <> UCase(rng.Cells(i).Value2) Then\n                str = str & IIf(CBool(Len(str)), delim, vbNullString) & _\n                        hdr.Cells(i).Value2\n            End If\n        End If\n    Next i\n\n    udfRogueHeaders = str\n\nEnd Function\n<\/code><\/pre>\n
In P2:Q2 as,<\/p>\n
=udfUniqueList(A2:L2)\n=udfRogueHeaders(A2:L2, A$1:L$1)\n<\/code><\/pre>\n
Results<\/a>:<\/strong><\/p>\n
\"udfRogueHeaders\"<\/a><\/p>\n
\n
\u00b9 The TEXTJOIN function<\/a> is being introduced with Excel 2016 \u2044 Office 365 \u2044 Excel Online. It is not available in earlier versions.<\/sub><\/p>\n
\u00b2 The new CONCAT function<\/a> for Excel 2016 \u2044 Office 365 \u2044 Excel Online is intended to replace the older CONCATENATE function<\/a> with improved functionality.<\/sub><\/p>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            0<\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved Formula for unmatched row cell and display value in one column <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function\u00b9 and the updated CONCAT function\u00b2 have difficulty with conditional concatenation beyond TEXTJOIN’s blank\/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,1046,2532,1621,401],"class_list":["post-8987","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-excel-formula","tag-formulas","tag-row","tag-vba"],"yoast_head":"\n[Solved] Formula for unmatched row cell and display value in one column - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Formula for unmatched row cell and display value in one column - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function\u00b9 and the updated CONCAT function\u00b2 have difficulty with conditional concatenation beyond TEXTJOIN’s blank\/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-16T10:02:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Formula for unmatched row cell and display value in one column\",\"datePublished\":\"2022-09-16T10:02:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\"},\"wordCount\":123,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png\",\"keywords\":[\"excel\",\"excel-formula\",\"formulas\",\"row\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\",\"name\":\"[Solved] Formula for unmatched row cell and display value in one column - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png\",\"datePublished\":\"2022-09-16T10:02:13+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Formula for unmatched row cell and display value in one column\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Formula for unmatched row cell and display value in one column - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Formula for unmatched row cell and display value in one column - JassWeb","og_description":"[ad_1] Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function\u00b9 and the updated CONCAT function\u00b2 have difficulty with conditional concatenation beyond TEXTJOIN’s blank\/no-blank parameter. Here are a pair of User Defined Functions (aka UDF) that will perform the tasks. Function udfUniqueList(rng ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/","og_site_name":"JassWeb","article_published_time":"2022-09-16T10:02:13+00:00","og_image":[{"url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png","type":"","width":"","height":""}],"author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Formula for unmatched row cell and display value in one column","datePublished":"2022-09-16T10:02:13+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/"},"wordCount":123,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png","keywords":["excel","excel-formula","formulas","row","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/","url":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/","name":"[Solved] Formula for unmatched row cell and display value in one column - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png","datePublished":"2022-09-16T10:02:13+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#primaryimage","url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Formula-for-unmatched-row-cell-and-display-value-in.png"},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-formula-for-unmatched-row-cell-and-display-value-in-one-column\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Formula for unmatched row cell and display value in one column"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/8987","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=8987"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/8987\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=8987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=8987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=8987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}