Hi,
I need please to do 2 things in the attached file:
1. Find in B value from column A, if value found replace in column C. I made an example for the first row which value is found, if no found, keep the same value from column A.
2. Find in D value from column A, if value found, make different background in A.
Thanks!
Dandi10
Can you clarify point 1?
If A2 contains whatever you enter in B2, do you want the whole cell content replaced by whatever you have in C2, or just the bit that matches B2?
2. CF in A2 to fill it if it matches D2 is "=search(D2,A2)"
Ochimus
Hello. Let me reply:Originally Posted by Ochimus
1. Just the bit that was found, not the entire data
2. Sorry didn't understand your question.. Can you explain it?
dandi10,
It's the Conditional formatting you need to enter in Col A. It means look in A2 for whatever is in D2. If you find it, fill A2 with whatever conditions you set (colour, font, etc).
Ochimus
Thank you. Can you please help me with the first point?
Also, I tried this formula and only gives a number, I need to find and fill color. Can you help?
@dandi10
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
OK. Hope someone can help, should be easy formulas.
I guess you want to partial/exact match "Contains" in A2, then replace with acronym?
I suggest a table of replacement in E21:E26
B2, string found:
C2, replace
Please Login or Register to view this content.
back colour of A2 change if "contains" found:Please Login or Register to view this content.
In CF menu:
Please Login or Register to view this content.
Quang PT
Thank you very much but doesn't work properly. I made some testes in column F, doesn't mark background, it shows true/false but doesn't finds well.
In regards the replace, it doesn't work too
Try to restart from beginning. Input manually your expected results and explan where they come from
Attached does what you want. Sorry for attaching screenshot, but for some reason the site won't upload the file!
SUBSTITUTE:
Put this in C2 and copy down:
=IF(B2="","",SUBSTITUTE($A2,PROPER($B2),INDEX(Replace,MATCH(B2,Contains,0))))
"Contains" is the range in Col F containing whatever words and values you want to change. "Replace" is the range in Col G listing what you want to replace them with.
Col A has a series of sentences.
Col B has DropLists of the entries in Col F. Select in any row which text or value you want to replace.
If your choice is in the sentence in Col A, the formula will replace that value with whatever you entered in Col G, so Col C will include the "amended" content.
E.g:
You selected "Richard" in B2, which is in A2. So the formula replaces the name with "Red" in Col C.
You selected "Venetia" in B4, but it is not in A4., so C4 is a copy of A4
A6 contains "George" but nothing appears in Col C because there is nothing in B6.
HIGHLIGHT:
Conditional Format formula in A2 is "=AND($D1<>"",ISNUMBER(SEARCH(D1,A1))), set to Range $A:$A.
Col D contains whatever words or values you want
If D2 is in A2, then A2 fills in pink. If it is not it stays clear.
(E.g. D2 - D4 all have "was". As that is in A2 and A3 they are filled, As it is not in A4 it stays clear.
Ochimus
Last edited by Ochimus; 11-25-2020 at 07:03 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks