36415

Excel: Concatenate Cells and Remove Duplicates

Question:

how can I concatenate the Values in Cells B1:K1 to receive the string in A1. Empty cells should be omitted. Is this possible using Excel commands or only with vba?

<a href="https://i.stack.imgur.com/UX5SH.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/UX5SH.png" data-original="https://i.stack.imgur.com/UX5SH.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Answer1:

If you have Office 365 Excel then you can use an Array form of TEXTJOIN:

=TEXTJOIN(", ",TRUE,INDEX(1:1,,N(IF({1},MODE.MULT(IF((IFERROR(MATCH(B1:K1,B1:K1,0)=COLUMN(B1:K1)-MIN(COLUMN(B1:K1))+1,0))*(B1:K1<>""),COLUMN(B1:K1)*{1;1}))))))

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

<a href="https://i.stack.imgur.com/gbUud.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/gbUud.png" data-original="https://i.stack.imgur.com/gbUud.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

<hr />

If you do not have office 365 Excel you will need vba. This UDF will mimic the TEXTJOIN. Put it in a module attached to the workbook and use the formula as described above.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr) Dim d As Long Dim c As Long Dim arr2() Dim t As Long, y As Long t = -1 y = -1 If TypeName(arr) = "Range" Then arr2 = arr.Value Else arr2 = arr End If On Error Resume Next t = UBound(arr2, 2) y = UBound(arr2, 1) On Error GoTo 0 If t >= 0 And y >= 0 Then For c = LBound(arr2, 1) To UBound(arr2, 1) For d = LBound(arr2, 1) To UBound(arr2, 2) If arr2(c, d) <> "" Or Not skipblank Then TEXTJOIN = TEXTJOIN & arr2(c, d) & delim End If Next d Next c Else For c = LBound(arr2) To UBound(arr2) If arr2(c) <> "" Or Not skipblank Then TEXTJOIN = TEXTJOIN & arr2(c) & delim End If Next c End If TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim)) End Function

Recommend

  • LoginHandler with ldapjs and Meteor.methods
  • Excel: find matrix cell that contains in a string and return same row but different column
  • Excel, Array Formulas, N/A outside of range, and ROW()
  • Difference in gcc -ffp-contract options
  • Custom valueOf for a function in JS
  • Writing the correct IDisposable implementation for classes with COM objects
  • Visual Studio Team Services: What permissions are needed to create team projects?
  • Linux Buffer Overflow Environment Variables
  • ASP.NET Core Identity & Cookies
  • Syntax error, command unrecognized. The server response was ''
  • Jackon 2.4.2 failing to deserialize valid date, even after specifying date format
  • Formula to remove entire words that start with certain characters
  • Build Settings : What changes should I do in the build settings so that on releasing the app it will
  • Azure AD: How to get group information in token?
  • ADO.NET provider with invariant name 'System.Data.SqlClient;' cannot be found (Entity Fram
  • Cannot access sitecore item field via API
  • How to search for a record and then delete it
  • Process.PrivateMemorySize64 returning committed memory instead of private
  • How to run .java file with Variable Name
  • How to implement arriving behavior with time delta?
  • Embedded Google Maps in Rails not responsive
  • Getting media player state in windows phone 7
  • How do I get HTML corresponding to current DOM tree?
  • Test if a set exists before trying to drop it
  • MongoDB in PHP using aggregate to group by _id is null not working
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • How to show dropdown in excel using jrxml (jasper api)?
  • jQuery tmpl and DataLink beta
  • Rearranging Cells in UITableView Bug & Saving Changes
  • SQL merge duplicate rows and join values that are different
  • Proper way to use connect-multiparty with express.js?
  • How to set the response of a form post action to a iframe source?
  • Angular 2 constructor injection vs direct access
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • Conditional In-Line CSS for IE and Others?