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

JuanD

New member
Joined
Oct 6, 2017
Messages
6
Reaction score
0
Points
0
Location
KY
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
 
Hi
please post a small sample sheet with some data and expected results ( no images pse)
 
here is a mock up with sample data
 

Attachments

  • lookuptest.xlsx
    12.6 KB · Views: 10
What is the logic used to determine which cells to concatenate together for each row?
 
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?
 
Is attached any good?
 

Attachments

  • ExcelGuru8482lookuptest.xlsm
    18.1 KB · Views: 13
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.
 
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
 
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
 
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:
I appreciate the additions, but I'm the only user and I never make mistakes (HA!). I'll incorporate those lines of code next time working with it. Again, many thanks.
 
Back
Top