Flexible Concatenation Function

**Rick Rothstein** · 02-01-2013, 11:24 PM

Originally Posted by snb

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

Code:

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

**snb** · 02-02-2013, 02:30 AM

**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

Originally Posted by javanchi

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

Originally Posted by Rick Rothstein

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

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)?

**javanchi** · 02-14-2013, 04:25 AM

Originally Posted by Rick Rothstein

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

Originally Posted by javanchi

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...

Code:

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

Originally Posted by Rick Rothstein

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

Originally Posted by javanchi

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?

Code:

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).