Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Select multiple and variable text cells and concatenate them in another cell

  1. #1
    Seeker JuanD's Avatar
    Join Date
    Oct 2017
    Location
    KY
    Posts
    6
    Articles
    0

    Select multiple and variable text cells and concatenate them in another cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am required to type into an excel spreadsheet cell the description of the steps that I have applied to a process. There are 10 steps commonly used and I have entered each step into 10 worksheet cells. Every time the process is run, it may require any number of or all 10 steps. I currently use =concat(a1, b1, c1, d1...) to populate the target cell editing the formula each time to choose only the cells containing the steps used in the process.

    I am looking for a way to not have to edit the formula every time to populate the step descriptions into the target cell.

    i have tried using vlookup, indirect and index, referencing a cell with name ranges for the cells containing the step descriptions, but cannot figure out how to get only the step descriptions i want and not getting errors for the step descriptions i don't want.

    What say you excel gurus?

    JuanD

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,192
    Articles
    0
    Hi
    please post a small sample sheet with some data and expected results ( no images pse)
    Thank you Ken for this secure forum.

  3. #3
    Seeker JuanD's Avatar
    Join Date
    Oct 2017
    Location
    KY
    Posts
    6
    Articles
    0
    here is a mock up with sample data
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,386
    Articles
    0
    What is the logic used to determine which cells to concatenate together for each row?


  5. #5
    Seeker JuanD's Avatar
    Join Date
    Oct 2017
    Location
    KY
    Posts
    6
    Articles
    0
    Not sure I understand your question. Which ever steps that were actually used in trouble shooting are the cells that I join together. i.e. if the chassis was opened all the connections are checked and diagnostics are run i use (OC), if a boot mode was changed then CB, if the 1st USB failed, changed it (CU) and all these are listed together in the notes field. Some units only get one or two comments (descriptions) others 7 or 8. Make sense?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,273
    Articles
    0
    Is attached any good?
    Attached Files Attached Files

  7. #7
    Seeker JuanD's Avatar
    Join Date
    Oct 2017
    Location
    KY
    Posts
    6
    Articles
    0
    p45cal

    I'm not sure what you did. I'm supposing the function "=ccat()" is a user defined function built using VB, but my digital guardian at work won't let me open this workbook for editing so I can't copy it. Can you send me the text of the VB function? Thanks. BTW it seems to work perferctly in the sample worksheet.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,273
    Articles
    0
    Preferably in a standard code-module rather than a sheet's code-module:
    Code:
    Function ccat(myref, lookuptable)
    zz = Split(Application.Trim(myref), ",")
    If UBound(zz) >= 0 Then
      For Each Z In zz
        x = Application.VLookup(Z, lookuptable, 2, False)
        If Not IsError(x) Then If IsEmpty(myresult) Then myresult = x Else myresult = myresult & vbLf & x
      Next Z
    Else
      myresult = ""
    End If
    ccat = myresult
    End Function

  9. #9
    Seeker JuanD's Avatar
    Join Date
    Oct 2017
    Location
    KY
    Posts
    6
    Articles
    0
    Yeah, that's what I'm talking about! Works like a charm. Never messed with VB, always used the Excel VB lite to do macros. Thanks for you help p45cal.

    JuanD

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,273
    Articles
    0
    There is one change I'd make, to make it more tolerant of spaces people might put in the list, that is the likes of:
    CB, NN, NF, HF , PS
    being treated the same as:
    CB,NN,NF,HF,PS

    Change from:
    x = Application.VLookup(Z, lookuptable, 2, False)
    to:
    x = Application.VLookup(Application.Trim(Z), lookuptable, 2, False)

    ps. but CB,NN,N F,HF,PS would still fail to produce the NF string because of the space between N and F
    If you wanted, it could be programmed to deal with that too with:
    x = Application.VLookup(Replace(Z, " ", "", 1, -1, vbTextCompare), lookuptable, 2, False)
    Last edited by p45cal; 2017-10-12 at 02:32 PM.

Page 1 of 2 1 2 LastLast

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
  •