# Thread: If and look up functions

1. Hi

I have a sheet with emloyee names on who have completed training sessions in the past. I have a second sheet where names are entered when they start training.
I am linking the new names onto the old training sheet and need a "warning" when a name appears which has already been entered.
This is problably easier to explain on the attached sheet (which is a very much shortened version)

I would be grateful if someone could advise me on the best way of doing this

Many thanks

Sarah

2. [quote name='sarahf' post='762773' date='02-Mar-2009 12:33'][/quote]
I'm not sure I understand your question correctly, but perhaps the attached version does what you want.
I modified the existing formulas slightly, and used a combination of formulas and conditional formatting in column D

3. thanks Hans, that is just what i was after, apologies for the confused explanation!

4. Hi Hans

I have a complication in that the formula is working on some lines and not others.

Have attached workbook once again with an example of a name which is not matching

5. There's a space after "BARNES" in cell B7 but not in B6. Hence they are different.
I'd check on the full name, by the way (column C or D), not on the last name only.

6. [quote name='sarahf' post='762815' date='02-Mar-2009 15:16']I have a complication in that the formula is working on some lines and not others.[/quote]
The name is not matching because it is DIFFERENT. One name is "Ian Barnes" and the other is "IAN BARNES ". If you need to do a compare that is case insensitive then you could try using VLookup.

Try the formula =IF(ISERROR(VLOOKUP(D7,D\$56,1)),"","Already Entered") in cell E7 of your latest workbook and see if that does what you want.

7. hi

I can see what you mean about the additional space between names and this is causing a problem. I cant understand why its doing this as the full names are all created using the same formula.

I have attached 3 of the names which are not working. Do you have any idea why an extra space is appearing? IF the space is taken out of the" " formula, then it works. I am mystified!

Sarah

8. Your workbook contains links to 'H:\Excel\[Manpower WHTerm February 200931 ver 1 (2).xls] west.h Sch 1', so we can't check the source. But apparently some of the names on that sheet have a trailing space. I have made the trailing spaces visible in columns C and D in the attached version (cells highlighted in green). Probably a data entry error.

[attachment=82592:Woody_query_2.xls]

9. Is there any way of correcting this without re entering all the names? Very frustrating!

Have attached a copy of the link if thats any help

10. [quote name='sarahf' post='763018' date='03-Mar-2009 06:36']Is there any way of correcting this without re entering all the names? Very frustrating![/quote]

In E15 you can use something like:
=trim(IF(D15="","",C15&" "&D15))

to remove leading and trailing spaces as well as extra spaces

Steve

11. [quote name='sarahf' post='763018' date='03-Mar-2009 12:36']Is there any way of correcting this without re entering all the names? Very frustrating!

Have attached a copy of the link if thats any help[/quote]
You could run the following macro while the sheet with the problem data is open:
Code:
```Sub Trimcells()
Dim oCell As Range
Application.Cursor = xlWait
For Each oCell In ActiveSheet.UsedRange.Cells
If Application.IsText(oCell) And Not oCell.HasFormula Then
oCell = Trim(oCell)
End If
Next oCell
Application.Cursor = xlDefault
End Sub```

12. that seems to have done the trick

many thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•