Two staff with the same names

[GHI1968]

We have two agency drivers with the same first and second names but they do have different middle names.

The sheet we use picks up certain drivers and adds a comment of 'YES' into a column so we know what drivers have a class 2 licence.

The sheet also sorts and removes drivers middle names to make sheets easier to read.

This has not been a problem until we have two with the same name 'Leslie Miller'.

I would like to pick up Leslie Thomas Miller so it adds the YES comment but can't work out how to do this if anyone can help please?

I have added an attachment to show how I pick up the information now.Excelforum.docx

[sandy666]

Attach example Excel file

In attached Excel file try not to use:

• merged cells
• password protection
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zooming/grouping/freezeing

You have words to logically describe the problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[GHI1968]

TRG Sheets - Excelforum.xlsmExample of TRG.xlsx

The sheet TRG is the one we use to sort the information we get and produce sheets and put it into the style etc we want.

The example sheet is how we are sent the information.

I need it to pick up that Leslie Thomas Miller is a class 2 driver and adds the 'YES' comment. Leslie Lee Miller should have the comment 'X'

[GHI1968]

Should have added that the drivers will be in different rows each day depending on their start times and days off etc.

[sandy666]

You can add unique ID for each driver then define Class 2 there where you want
There are middle names so ???
also I don't know where you want insert "Class 2" so I did it in column G on sheet1
and if Leslie Thomas Miller got Class 2 , another Leslie doesn't need "X"

[GHI1968]

Sandy,

If you copy and paste the information from 'Example of TRG' into the one named 'TRG Sheets' and then go from there it hopefully shows it better?

'Example of TRG' is an attachment we are sent, we run through 'Remove false start times' then 'Remove drivers middle names' then 'Sort into time order' this removes anything we don't need/use.

'Front desk to 07:00' and 'Front desk from 07:00' are the sheets we use and these have to show if a driver is class 2 or class 1 licence.

Your column assigning an id to each driver would work if that information was in column B on the TRG sheets on the Sort TRG tab if it can pick up from that to add the comment in column H??

[bakerman2]

If you change the names on your Class2 Drivers list as they appear in the Example of TRG (full names with middle names) and replace the remove middle names code with this

Please Login or Register  to view this content.

it should work just fine.

[GHI1968]

'Full' names could be a problem? Depending on who prepares the information we are sent the drivers names can change, Melvin Dudley is sometimes Mel Dudley etc

[GHI1968]

Is it possible to change the code for remove middle names to reduce middle names to just an initial? Leslie Thomas Miller would be Leslie T. Miller

[bakerman2]

Yes it is but that doesn't account for Melvin sometimes changing to Mel.
You should really try to find some uniformaty in the way data is handed over because that are things you can't solve with code.

[GHI1968]

If i change Melvin/Mel to just M Dudley it still works.

They should all work if its not a common name combination going forward.

Les T Miller works fine if i can work out how to concatenate the names to change middle names to an initial only? This would pick up he is class 2 and thats all I need it for.

[GHI1968]

Bakerman,

What should really happen is we tell the agency to sort all this out for us!

[bakerman2]

Please Login or Register  to view this content.

[GHI1968]

That works Bakerman!!!

A BIG thank you to everyone with this, it was the last thing that was causing problems....... now fixed

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[bakerman2]

Thanks for feedback and rep+.