Forum Discussion
letter by letter comparison
- Aug 11, 2018
I think I've done.. I hope so anyway.
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()