Forum Discussion
letter by letter comparison
Hi everyone,
I need to compare the contents of a cell with a certain column.
Let say we have a column with one character in each cell, I need excel to erase/delete (or change the filling color of) the first match from this column once I write it in a certain defined cell.
Eg. if I put (W) in that cell, then the first (W) in that column will disappear (or filling color changes).
Thank you.
I think I've done.. I hope so anyway.
19 Replies
Hi Mahmoud,
I've updated the file with the solution.
It now has two sheets, the first to hide the letters, and the second to change its font color.
You will find some helper cells on the right side of each sheet, you must keep these cells, if they annoy you, you can hide their column
If you want to test a new text, you should first type it into the Full Text cell, and then test it in cell B1.
Hope that helps
Dear Haytham,
There is a bug there, you see if I wrote any letters that are NOT in the column (which will happen all the time), then excel will mistakingly delete or change color of the first letters as if it is identical, while it is not.
Please find the attached.
Thank you ...
Dear Mahmoud,
Sorry, but I said in the previous reply that you should first type it into the Full Text cell, and then test it in cell B1.
Hi Mahmoud,
Please find the supposed solution in the attached file.
Regards
Thank you Haytham,
I found no solution in the attached file, no formula in any cell.
Basically, we should have a column with text, and once we paste text in a certain defined cell, the same letters disappear from the column (or change cell filling color).
Thank you.
In fact, there is a formula in the worksheet, but it's hidden in a conditional formatting rule.
=IF(AND(COUNTIF(C:C,C1)>1,COUNTIF($C$1:C1,C1)=1),TRUE,FALSE)
It seems that this isn't what you want, I think I didn't understand your question well, so I'll try with another solution and refer back to you if I got it.
Regards
So, you have the phrase split on letters located one by one down in column. When you type same phrase in another cell you'd like to hide letters in columns if no mistakes and keep visible typos. Like this?
Yes ... exactly ... the fixed text is inside the column, I want the test text (of the cell) to be identical with the target text (of the column), the exact same letters, but not necessarily the same order.
If somehow I could get an error message if (for example) I entered (W) in the cell while all the (W)s of the column were already consumed, that would be great.
Thank you ...
I think I understand your problem, and have come up with an answer. This isn't going to delete the letter, but it can change the formatting of the cell that contains the first instance of that letter.
If you wanted it deleting, that would be possible with VBA, but i've chosen to try and solve this without code.
I've attached a spreed sheet that has a table with 2 columns, 1 and 2. They both do basicly the same thing, but column 1 uses a helper cell (C5) to make the conditional formatting easier to understand, where as column 2 does everything in the conditional formatting rule. I'll explain both and you can adapt to your own needs.
In C5 with use the CELL formula to give us the row number from an index(match) look up. In other words we use index(match) to return the first instance of whatever is in B5, and then the CELL tells us what row number that result was in.
Column1 then has a conditional format that says, if =row() = $C$5 change red. I.e if this row number matches the row number we looked up in c5 then colour it red.
Column2 is the same, except there is no helper cell. The rule for the formatting looks like this. The indirect are used here as I was using structured references. If you aren't using tables you might not need them.
=CELL("row",INDEX(INDIRECT("Table3[Column2]"),MATCH($B$5,INDIRECT("Table3[Column2]"),0)))=ROW()
