i know i might be pushing limits but is there a way in excel to identify letters in a cell and format accordingly, meaning if i type "TAT 12 hello" in a cell and type "TAT 12 bye" in another than both the cells should become red matching the code - "TAT 12" as it is in both the cells, like it happens in case of duplicated values using conditional formatting.
Conditional formatting > New Rule > Use a formula to determine which cells to format
Then you need to write the formula. To find out if "TAT 12" appears in the column, select the column by clicking on the column letter, then write the following formula (I've written it for Column A):
=NOT(ISERROR(FIND("TAT 12",A1)))
In other words, format the cell if the FIND doesn't return an error.
Then use the Format button to choose the formatting you want to apply.
Yes.. it can be possible... but you need to define the length of the character to which you need that to be matched with another cell... e.g., TAT 12 is 6 in length (including space).. please confirm back. thanks.
And also.. do you want to always search from left side?
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Sorry, missed that you want to type TAT 12 into a cell, just change the formula to (assuming the cell you're typing into is B1).
=NOT(ISERROR(FIND($B$1,A1)))
NB The reference to the input cell must be an absolute reference (i.e. with $$)
I am looking for partial uniqueness matching some percentage of content, its not specific to one value like "TAT 12" that i mentioned. Suppose a column has "TAT 12 hello" matching with another cell "TAT 12 bye" in the same column, another cell in the same column "MTR 14 hello" matching with "MTR 14 bye", then these four cells could be marked duplicate in a certain way?
Dilip - yes character length can be fixed and yes it has to start from left
Last edited by m_k; 04-06-2012 at 08:52 AM.
Hi m_k,
See the attached file where I have highlighted the duplicates using conditional formatting.. I have considered 6 characters from left side of the cell value. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Thanks Dilip..
can you explain how this is working =COUNTIF($B$1:$B$50,$B1)>1, i cant seem to follow it, how is B1 helping here??
you are welcome M_K.. cheers
B1 has the # sign with B that means Column B is / will be fixed when you drag the formula down.. in the range of B1:B50.. so conditional formatting logic is checking the count of every cell (row by row but in column B only) in the range B1:B50.. and if that count is greater than 1.. then it is TRUE.. means go ahead for conditional formatting logic to get applied.. thanks.
Feel free to get back in case of any doubts.. and if you are satisfied with the help, please mark this thread as solved.. see the forum rules to do so..
Regards,
DILIPandey
<click on below 'star' if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks