Excel "compare text string" help

  AragornUK 13:24 11 Feb 06
Locked

Hello all. It's been agood while since I was here, but I decided to dip in again, see who's about. So, any Excel gurus in today?

I have 2 worksheets (for the example, called S1 and S2). I have a list of names on S1 and require a formula that, when I type a name in S2 it will check column A in S1 and highlight if the name is a duplicate. So far so good. I managed this quite easily.

However, the person I'm working on this for informed me that the people using the spreadsheet aren't necessarily the best typers, so there could be leading/trailing spaces in EITHER list. I've managed to incorporate TRIM into the function to shed excess spaces in the list on S2, but trying to put TRIM into the VLOOKUP doesn't seem to be working.

Is there any way of allowing for these extra spaces in the original list, or even easily removing them at time of text entry?

The function I have now is this:

=IF(ISNA(VLOOKUP(TRIM(A4),S1!$A$1:$A$600,1,FALSE)),"","D")

  VoG II 13:59 11 Feb 06

The reason it won't work is because TRIM is only working on the value to be looked up, not the range that is being looked up.

You could strip the spaces using the following macro

Sub tst()
Dim iRow, txt As String
For iRow = 1 To 600
txt = Sheets("S1").Cells(iRow, 1).Value
Sheets("S1").Cells(iRow, 1).Value = Trim(txt)
Next iRow
End Sub


Then to prevent further entries with spaces, right click the sheet tab, select View Code and enter the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Trim(Target.Value)
End Sub

  AragornUK 14:17 11 Feb 06

Thanks VoG.

Good to see you're still around ;o)

Will give it a try.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac