• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Description of cells numbers

I

IZ2018

Member
I have attached a file.
In Column K, I want the description of numbers defined in A and B
If cells are blanc, nothing and if numbers are not in A then Error message
 

Attachments

  • Concatenate.xlsx
    10.8 KB · Views: 7
I am looking at your desired results and do not understand how you achieved the expected. Please explain in Simple English your criteria. It is not intuitive from your example.
 

I understand the need which can be achieved by code
but as here it's not the appropriate VBA section
I let the formula experts help …​
 
In Column A are Numbers, in Column B are Description/Meaning of Numbers.
A and B are given.
From D2 to J2 I have these Numbers 1 4 19 and I want in Column K the description/meaning of them
 
If blank cell, Nothing, then for 1 say the Description of 1 which is in B2, Text100, then number 4, say description of 4 which is Text300 ,......
 
upload_2018-3-2_9-54-48.png

In K2, copied down :
=SUBSTITUTE(TRIM(IF(D2="","",IFERROR(VLOOKUP(D2,Table1,2,),"ERROR"))&IF(E2="",""," "&IFERROR(VLOOKUP(E2,Table1,2,),"ERROR"))&IF(F2="",""," "&IFERROR(VLOOKUP(F2,Table1,2,),"ERROR"))&IF(G2="",""," "&IFERROR(VLOOKUP(G2,Table1,2,),"ERROR"))&IF(H2="",""," "&IFERROR(VLOOKUP(H2,Table1,2,),"ERROR"))&IF(I2="",""," "&IFERROR(VLOOKUP(I2,Table1,2,),"ERROR"))&IF(J2="",""," "&IFERROR(VLOOKUP(J2,Table1,2,),"ERROR")))," ",CHAR(10))

Regards
Bosco
 

Attachments

  • ConcatenateText.xlsx
    11.8 KB · Views: 5
If the cell with number is Red, can the associated text to be red ?
If cell with number 4 in F2 is formatting RED, can the cell with Text300 in K3 be red
 
Thank you.
I the formula above, I would like to ask this : If texts are one word, like Text100 then Wrap is great but if is more then one word like if I replace cell B2 with :
Text 100 115
then Wrap will do this :
Text
100
115
and so one with others, which I do not want
So in formula I would like to put a line break in such a way that description of one number to stay in same line
 
IZ2018 said:
Thank you.
I the formula above, I would like to ask this : If texts are one word, like Text100 then Wrap is great but if is more then one word like if I replace cell B2 with :
Text 100 115
then Wrap will do this :
Text
100
115
and so one with others, which I do not want
So in formula I would like to put a line break in such a way that description of one number to stay in same line
Click to expand...
Then, the formula will grow longer:

=SUBSTITUTE(SUBSTITUTE(TRIM(IF(D2="","",IFERROR(SUBSTITUTE(VLOOKUP(D2,Table1,2,)," ","_"),"ERROR"))&IF(E2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(E2,Table1,2,)," ","_"),"ERROR"))&IF(F2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(F2,Table1,2,)," ","_"),"ERROR"))&IF(G2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(G2,Table1,2,)," ","_"),"ERROR"))&IF(H2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(H2,Table1,2,)," ","_"),"ERROR"))&IF(I2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(I2,Table1,2,)," ","_"),"ERROR"))&IF(J2="",""," "&IFERROR(SUBSTITUTE(VLOOKUP(J2,Table1,2,)," ","_"),"ERROR")))," ",CHAR(10)),"_"," ")

Regards
Bosco
 
You must log in or register to reply here.
Back
Top