Flexible Concatenation Function [Archive] - Excel, Access, PowerPoint and Word VBA Macro Automation Help

View Full Version : Flexible Concatenation Function

Rick Rothstein

02-19-2012, 11:22 PM

See this link FORMATTED Flexible Concatenation Function (http://www.excelfox.com/forum/f22/formatted-flexible-concatenation-function-582/) for this article's companion function.

Unfortunately, Excel's CONCATENATE function does not work with ranges or arrays (kind of making it worthless in my opinion since simply concatenating the text together using an ampersand produces the same result more compactly). However, the following UDF (user defined function), which can also be used as a function called from other VB code if desired, which I have posted in the past, does work with ranges...

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
Dim Index As Long, Rw As Long, Col As Long, Down As Boolean, Rng As Range, Cell As Range
If IsMissing(Delimiter) Then Delimiter = ""
Index = LBound(CellRanges)
Do While Index <= UBound(CellRanges)
If TypeName(CellRanges(Index)) = "Range" Then
Set Rng = CellRanges(Index)
If Index < UBound(CellRanges) Then
If TypeName(CellRanges(Index + 1)) <> "Range" Then Down = CellRanges(Index + 1) = "|"
End If
If Down Then
For Col = 0 To Rng.Columns.Count - 1
For Rw = 0 To Rng.Rows.Count - 1
If Len(Rng(1).Offset(Rw, Col).Value) Then
ConCat = ConCat & Delimiter & Rng(1).Offset(Rw, Col)
End If
Next
Next
Index = Index + 1
Else
For Each Cell In Intersect(Rng, Rng.Parent.UsedRange)
If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
Next
End If
Else
If CellRanges(Index) = "||" Then
ConCat = ConCat & Delimiter & "|"
Else
ConCat = ConCat & Delimiter & CellRanges(Index)
End If
End If
Index = Index + 1
Loop
ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

This function allows you to create formulas like this...

=ConCat("-",A1:B3,C1,"HELLO",E1:E2)

and the contents of the indicated cells, and the text constant "HELLO", will be concantenated together, in the order shown, with a dash between them. For rectangular ranges, the default direction of concatenation is across the columns of a row before advancing to the next row. If you follow the rectangular range with a pipe-symbol (|) in quotes, then the direction of processing changes to down the rows of a column before advancing to the next column. Consider the following portion of a worksheet and note the differing order of the number words in each rectangular range.

B

C

D

E

F

1

.

2

one

three

five

3

two

four

six

4

5

seven

eight

nine

6

ten

eleven

twelve

7

.

The formula...

=ConCat(", ",C2:E3,"|",C5:E6)

will produce this output...

one, two, three, four, five, six, seven, ten, eight, eleven, nine, twelve

Then "|" argument is not concatenated into the outputted text because it only serves as a signal to the function to concatenate down the columns before advancing across the columns. If the preceding argument was not a range, then the pipe symbol would have been outputted normally. So, you might be wondering, how, in the unlikely event you needed to, would you output a pipe symbol that follows a range without having it be interpretted as a signal to change the direction of concatenation? Simply double it up like this...

=ConCat(", ",C2:E3,"||",C5:E6)

If you needed to change the direction of concatenation and output a pipe symbol as well, just use two pipe symbols as the argument instead, like this...

=ConCat(", ",C2:E3,"|","|",C5:E6)

The first pipe symbol will be used as the direction of concatenation indicator and the second one as a simple text character. Note, the pipe symbol immediately following any range (even a single column or single row one) will be interpretted as an indicator to change the direction of concatenation. For single row or column ranges, following them with a pipe symbol or not following them with one, the output will be the same.

The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...

=ConCat("",A1:B3,C1,"HELLO",E1:E2)

or like this (note the leading comma)...

=ConCat(,A1:B3,C1,"HELLO",E1:E2)

your choice.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function (as shown in the examples above).

snb

06-08-2012, 02:53 AM

Hi Rick,

Nice function you wrote.
Using the ADO 3.0 library I came up with:

Function concat_snb(c00, c01, c02, c03)
With New ADODB.Recordset
.Open "SELECT [" & c00 & "] FROM `" & c01 & "$`;", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
concat_snb = .GetString(, , c02, c03)
End With
End Function

Sub snb()
MsgBox concat_snb(Range("D1").Value, ActiveSheet.Name, "|", vbLf)
End Sub

In this case you can choose:
- which field delimiter to use
- which row delimiter to use
- which fields to concatenate
- how many rows to concatenate

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

Admin

06-08-2012, 09:02 AM

Hi snb,

Welcome to ExcelFox !!

MS has documented that there is a memory leakage while you querying data from an open Excel.

BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) (http://support.microsoft.com/kb/319998)

Rick Rothstein

06-08-2012, 07:52 PM

Nice function you wrote.
Using the ADO 3.0 library I came up with:

Thanks for commenting. I have never had to deal with data base programming before, so I cannot comment on your code. However, I do notice that Admin has responded indicating that a problem may exist with your approach, so I'll just let things stand at that.

Rick Rothstein

09-06-2012, 11:05 PM

I have revised the code for the ConCat function in Message #1 (the original thread message) and am using this message as an alert to those who may be subscribed to this thread. I have added the ability specify which order cells in rectangular ranges should be concatenated in... read the revised description in Message #1 to see how to work with this new functionality.

snb

09-07-2012, 01:52 AM

concatenation row by row:

Function ConCat_snb(delimiter As String, ParamArray CellRanges() As Variant) As String
For Each it In CellRanges
If VarType(it) = 8204 Then
For j = 1 To UBound(it.Value)
concat_snb = concat_snb & delimiter & Join(Application.Index(it.Value, j), delimiter)
Next
Else
concat_snb = concat_snb & delimiter & it
End If
Next

ConCat_snb = Mid(concat_snb, 2)
End Function

concatenation column by column:

Function ConCat_snb(delimiter As String, ParamArray CellRanges() As Variant) As String
For Each it In CellRanges
If VarType(it) = 8204 Then
For j = 1 To UBound(it.Value, 2)
concat_snb = concat_snb & delimiter & Join(Application.Transpose(Application.Index(it.Va lue, , j)), delimiter)
Next
Else
concat_snb = concat_snb & delimiter & it
End If
Next

ConCat_snb = Mid(concat_snb, 2)
End Function

nlk.public

01-31-2013, 05:46 AM

Rick Rothstein

01-31-2013, 03:30 PM

Rick, brilliant UDF. Could this be modified to exclude duplicate values? Let's say the range A1,B1,C1,D1 has values of ABC,DEF,ABC,123. The desired result of the modified concat UDF would be to display in one cell: ABC,DEF,123 (cutting out the second ABC).

Doable???

Thank you for your nice comment about my UDF... much appreciated. As for you question... I am not sure, there is a lot going on in the code and I am not sure how easy it would be to modify it for your request. But I must ask... do you really think it is necessary to add a "Uniques Only" feature? I mean, the user is constructing the list that is to be concatenated... why would they duplicate a cell or set of cells if they don't want it in the output in the first place? It would seem the user would automatically filter his/her list to avoid duplicates... after all, the reason they are using the function in the first place is to craft the output string to look a certain way via the list they are inputting... I have trouble imagining a situation where they would duplicate cells they did not want duplicated and, if they did so by accident, it would seem easy enough for them to go back and modify the input list to remove the cells they did not actually want in their output text.

nlk.public

01-31-2013, 05:49 PM

Your questions are fair and I can say, at least for my application the ability to remove duplicates is 100% necessary.

The situation is such that we need to return, in a single cell, all the possible values that appear in column X. It's less the creation of a specific series of numbers, rather a listing of all the unique values. I assure you that all the values (duplicate or not) in column X need to exist.

If it is not feasible to build that functionality into the UDF, I accept defeat as an option ;)

Thank you for your nice comment about my UDF... much appreciated. As for you question... I am not sure, there is a lot going on in the code and I am not sure how easy it would be to modify it for your request. But I must ask... do you really think it is necessary to add a "Uniques Only" feature? I mean, the user is constructing the list that is to be concatenated... why would they duplicate a cell or set of cells if they don't want it in the output in the first place? It would seem the user would automatically filter his/her list to avoid duplicates... after all, the reason they are using the function in the first place is to craft the output string to look a certain way via the list they are inputting... I have trouble imagining a situation where they would duplicate cells they did not want duplicated and, if they did so by accident, it would seem easy enough for them to go back and modify the input list to remove the cells they did not actually want in their output text.

snb

02-01-2013, 10:49 PM

Rick Rothstein

02-01-2013, 11:24 PM

E.g. in cell K1: "=unique_sorted_concat_snb(A1:F10,",")

Function unique_sorted_concat_snb(c00, c01)
With CreateObject("System.Collections.ArrayList")
For Each cl In c00.SpecialCells(2)
If Not .contains(cl.Value) Then .Add cl.Value
Next
.Sort

unique_sorted_concat_snb = Join(.toarray(), c01)
End With
End Function
Thank you for posting this solution to the OP... it introduced me to something new as I was not aware of the ArrayList Class before now. I looked it up on line and it seems quite powerful. Thanks again.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

snb

02-02-2013, 02:30 AM

@Rick

If you want to know it's reference for early binding:
after adding the reference to mscorlib
you can use : with new arraylist

xx
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxbmrGQEyx1TQ_uArd4AaABAg
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxbmrGQEyx1TQ_uArd4AaABAg.9_Yw_txJweP9_YwjnrUB LX
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxbmrGQEyx1TQ_uArd4AaABAg.9_Yw_txJweP9_Yws1MJg OT
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxbmrGQEyx1TQ_uArd4AaABAg.9_Yw_txJweP9_Yww_BsT _F
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxbmrGQEyx1TQ_uArd4AaABAg.9_Yw_txJweP9_Yx3H3Fz z-
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=Ugxmp6XU3AhXRuCqlvJ4AaABAg.8cn10kQZYX-9_Z95ndHPfg
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgzEKGm91f8RPhKqOZx4AaABAg
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxkCAv0MhWMg_AEZpB4AaABAg
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxkCAv0MhWMg_AEZpB4AaABAg.9_agDACTsIl9_agK7mqF 3_
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxkCAv0MhWMg_AEZpB4AaABAg.9_agDACTsIl9_agOHnXf wt
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxkCAv0MhWMg_AEZpB4AaABAg.9_agDACTsIl9_agVgrCH bu
https://www.youtube.com/watch?v=IHrGresKu2w&lc=UgxkCAv0MhWMg_AEZpB4AaABAg.9_agDACTsIl9_agcZsbO aJ
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=Ugwba6HKcmdePQ37THJ4AaABAg
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=Ugwba6HKcmdePQ37THJ4AaABAg.9_anInU6pmm9_anaTqNF bY
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWtNKIx Wh
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWnxeBT fV
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWfQA00 Vr
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWaIdJe P5
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWWwa8C qr
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWSEF2g K0
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg.9_dWE5XFMqR9_dWK0cUE q8
https://www.youtube.com/watch?v=f9xPJXslVWE&lc=UgwYYM68y2wFizmKXgJ4AaABAg
https://www.youtube.com/watch?v=EGJZocG8W2E&lc=UgwVXZZzn0s1uEeZ7fB4AaABAg.9_dir7Hy9FJ9_dj1hsi4-B
https://www.youtube.com/watch?v=EGJZocG8W2E&lc=UgwVXZZzn0s1uEeZ7fB4AaABAg

javanchi

02-14-2013, 12:41 AM

Rick, Is there a way to concatenate lists of data that are of variable lengths set apart by indicators?

Example:

----------
a
b
c
----------
e
f
g
h
i
j
----------
k
l
m
n
----------

Thanks,

JOHN

Rick Rothstein

02-14-2013, 01:21 AM

Rick, Is there a way to concatenate lists of data that are of variable lengths set apart by indicators?

Example:

----------
a
b
c
----------
e
f
g
h
i
j
----------
k
l
m
n
----------

Clarification.... are those values all in one cell or is each line in a cell by itself? If individual cells, could the "indicator" be an empty cell instead of a series of dashes (that would make coding easier)? Do you have a preferred delimiter for the resulting concatenated text?

javanchi

02-14-2013, 01:59 AM

Clarification.... are those values all in one cell or is each line in a cell by itself? If individual cells, could the "indicator" be an empty cell instead of a series of dashes (that would make coding easier)? Do you have a preferred delimiter for the resulting concatenated text?

Rick,

Each line is a separate cell. There is actually a blank line above each string of dashes that could be used as the indicator (see below for a real data stream). Preferred delimiter can be semi-colon, but not comma or colon, as these occur in the raw data stream. As you can see, some of what we have to deal with is a broken-up sentence which we will ultimately filter out. The important data is the somewhat cryptic information in the data stream. Our dataset has over 8000 entries that take up well over 250000 lines in Excel... trying to separate the wheat from the chaff is not so easy...

---------------------------------------------------
STAPHYLOCOCCUS AUREUS COLONY COUNT >100000 CFU/M
DD SS : CC(SS),FM(SS),GAT(SS),GM(SS)
OX(SS),SXT(SS),TE(SS),VA(SS)
R : PE+(R)
HIDE : E(R),RIFA(SS)
#NAME?
susceptible strains are RESISTANT
to Beta-lactamase-labile
penicillins, but SUSCEPTIBLE to
Beta-lactamase-stable penicillins
Beta-lactamase inhibitor
combinations, relevant Cephems,
and Carbapenems.

---------------------------------------------------
STAPHYLOCOCCUS AUREUS (MODERATE TO NUMEROUS)
MIC1 SS : AMC(<=4/2),CDM(<=0.5),E(<=0.5)
OX(<=0.25),SXT(<=0.5/9.5),TE(<=4)
HIDE : CFXS(<=4-SS)

---------------------------------------------------
STAPHYLOCOCCUS AUREUS AEROBIC AND ANAEROBIC
BOTTLE SET
DD SS : CC(SS),E(SS),GAT(SS),GM(SS)
OX+(SS),RIFA+(SS),SXT(SS),TE(SS)
VA(SS)
R : PE(R)
#NAME?
susceptible strains are RESISTANT
to Beta-lactamase-labile
penicillins, but SUSCEPTIBLE to
Beta-lactamase-stable penicillins
Beta-lactamase inhibitor
combinations, relevant Cephems,
and Carbapenems.
#NAME?
for chemotherapy.
MIC1 Unk : SYNCI(<=0.25)
SS : CC(<=0.25),CIP+(<=0.25),E(<=0.5)
GAT(<=0.5),GM(<=1),LVX(<=0.5)
OX+(0.5),RIFA+(<=1),SXT(<=2/38)
TE(<=1),VA(<=2)
R : PE(>8)
#NAME?
develop resistance during
prolonged therapy with quinolones
Isolates that are usually
susceptible may become resistant
within 3 to 4 days after
initiation of therapy.
#NAME?
susceptible strains are RESISTANT
to Beta-lactamase-labile
penicillins, but SUSCEPTIBLE to
Beta-lactamase-stable penicillins
Beta-lactamase inhibitor
combinations, relevant Cephems,
and Carbapenems.
#NAME?
for chemotherapy.

Rick Rothstein

02-14-2013, 02:25 AM

javanchi

02-14-2013, 04:25 AM

Sorry, two more questions...

1) Where did you want the output to go to and did you still want the EmptyCell/DashedCells separator between the concatenated text?

2) Is your "data" text constants or formulas (I notice you have some #NAME? errors in there)?

Rick, data can go into the adjacent columns to the right of the data fields. All is text, but that field shows an error because the cell starts with an "=".

thanks for taking a look at this...

JOHN

Rick Rothstein

02-14-2013, 05:49 AM

Rick, data can go into the adjacent columns to the right of the data fields. All is text, but that field shows an error because the cell starts with an "=".

I'm glad I asked about that #NAME? error as those lines of code required special handling. Give this macro a try...

Sub ConcatDashedRanges()
Dim NextRow As Long, TempText As String, EmptyCells As Range, EqualSignCells As Range, Ar As Range
On Error Resume Next
Set EqualSignCells = Columns("A").SpecialCells(xlFormulas)
For Each Ar In EqualSignCells
Ar.Value = "'" & Ar.Formula
Next
Set EmptyCells = Columns("A").SpecialCells(xlConstants)
For Each Ar In EmptyCells.Areas
NextRow = NextRow + 1
TempText = Application.Trim(Join(Application.Transpose( _
Ar.Offset(1).Resize(Ar.Count - 1).Value), ";"))
If Left(TempText, 1) = "=" Then TempText = "'" & TempText
Cells(NextRow, "B").Value = TempText
Next
End Sub

I highlighted in red the location where the delimiter is specified in case you want to change it in the future.

javanchi

02-15-2013, 08:55 PM

I'm glad I asked about that #NAME? error as those lines of code required special handling. Give this macro a try...
I highlighted in red the location where the delimiter is specified in case you want to change it in the future.

Rick,

The macro worked very nicely. My team is very impressed!!

Can we nuance it to align the concatented line directly to the right of its starting point rather than at the top of the column?

JOHN

Rick Rothstein

02-15-2013, 09:42 PM

Rick,

Can we nuance it to align the concatented line directly to the right of its starting point rather than at the top of the column?

Is this what you are looking for?

Sub ConcatDashedRanges()
Dim TempText As String, EmptyCells As Range, EqualSignCells As Range, Ar As Range
On Error Resume Next
Set EqualSignCells = Columns("A").SpecialCells(xlFormulas)
For Each Ar In EqualSignCells
Ar.Value = "'" & Ar.Formula
Next
Set EmptyCells = Columns("A").SpecialCells(xlConstants)
For Each Ar In EmptyCells.Areas
TempText = Application.Trim(Join(Application.Transpose( _
Ar.Offset(1).Resize(Ar.Count - 1).Value), ";"))
If Left(TempText, 1) = "=" Then TempText = "'" & TempText
Ar(1).Offset(1, 1).Value = TempText
Next
End Sub
If you want the output aligned with the dashes instead of the first text line, change the red highlighted 1 (one) to a 0 (zero).

snb

02-16-2013, 09:47 PM

Rick Rothstein

02-16-2013, 11:37 PM

or ?

Sub ConcatDashedRanges()
Columns(1).replace "=","'="

For Each Ar In Columns(1).SpecialCells(2).Areas
Ar(1).Offset(, 1) = Application.Trim(Join(filter(filter(Application.Tr anspose(Ar.Value),"--",false),"'=",false), ";"))
Next
End Sub
Your code does not appear to include the cells that started with the equal sign within the concatenated output from the macro (at least on my tests it doesnt').

snb

02-17-2013, 03:58 AM

That's correct; they have been filtered out.
You can remove the filter that does that: filter(...,"'=","")

Rick Rothstein

05-11-2019, 08:22 PM

Rick, brilliant UDF. Could this be modified to exclude duplicate values? Let's say the range A1,B1,C1,D1 has values of ABC,DEF,ABC,123. The desired result of the modified concat UDF would be to display in one cell: ABC,DEF,123 (cutting out the second ABC).

Doable???

I know I am a few years late with this, but here is a function that with take a delimited text string (which is the output from my ConCat function) and returns the same delimited text with duplicate removed. So, to accomplish what you want, simply pass the output from the ConCat function into the first argument for the Uniques function below and specify the delimiter in the second argument... the output from the Uniques function will be what you asked for.

Function Uniques(Text As String, Delimiter As String) As String
Dim X As Long, Data() As String
Data = Split(Text, Delimiter)
With CreateObject("Scripting.Dictionary")
For X = 0 To UBound(Data)
.Item(Data(X)) = 1
Next
Uniques = Join(.Keys, Delimiter)
End With
End Function