Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Flexible Concatenation Function

  1. #11
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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
    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/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    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=UgzMCQUIQgrbec400jl4AaABAg
    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=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    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=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:57 PM.

  2. #12
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Last edited by DocAElstein; 09-29-2022 at 07:47 PM.

  3. #13

  4. #14
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by javanchi View Post
    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?

  5. #15
    Junior Member
    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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.

  6. #16
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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)?
    Last edited by Rick Rothstein; 02-14-2013 at 02:27 AM.

  7. #17
    Junior Member
    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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

  8. #18
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by javanchi View Post
    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.
    Last edited by Rick Rothstein; 02-14-2013 at 05:54 AM.

  9. #19
    Junior Member
    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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

  10. #20
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by javanchi View Post
    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).
    Last edited by Rick Rothstein; 02-15-2013 at 09:46 PM.

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. FORMATTED Flexible Concatenation Function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 1
    Last Post: 10-14-2012, 03:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •