Find unique names in certain range.

Hello:

Please refer to Attached File...
I have employees clockIn-Out info in "Daily_Receipt" sheet in cell O66:EH111.
Each day you may have different employees.
I need a either a VB code or formula to list unique list of employees in column K, cell K66 downwards, aphabettically.

I have manually entered in column K, Cell K66 downwards.

Let me know if you have any questions.

Thanks

Riz

Can the "names" really begin with numbers, as shown in O71??

In fact, if you can dump the digits... this array formula works. This is set up to put numbers then letters... I'm about to head out & won't be back for hours, so i'll not have a chance to see if I can get it to reverse the order of appearance (words, then numbers).

Sorted. Even with the numbers there. Should you want zero not to appear, you'll probably need to put an "if" formula round it all.

Hi Glenn & Others:

i think there is small misunderstanding.
The formula is just doing the list for just one column O.
I need to go to each column O, S,W (Every 4th column) and add all of the names and sort out unique names alphabetycally.
Let me know if you have any questions.

Riz

Hi Nilem or other:

I need to modify the code to fit in my sheet.
Basically i want to check the employee names in O601:O640, and then every 4th column. We may have blank cell content., ignore blank cells.
Also i mentioned earlier need to ignore any numbers.
We need a unique employee names only from all the column (4th Column from Column O and last column EE).

Basically i think the code should do the below to come to result:
1. Code needs to collect all names from Cell O601:O640,S401:S440, and so on.
2. Search for blank cells, if it finds any blank cells then delete.
3. Delete duplicate names.
4. sort out alphabetically.
5. Paste at K600.

Let me know if you have any questions.

Thanks
Riz


Let me know if you have any questions.
Riz

Hi,
Here's another suggestion, similar to nilem's but using the arraylistobject to store and sort the names :

Please Login or Register  to view this content.

Maybe this isn't elegant; but it does work; providing you can take a helper row. I need one to get a continuous named range. Currently it's on sheet1, but it can go anywhere. Just call the named range "List" and the array will work perfectly. I hope.

Hi Nilem:

Below is the problem i am facing, i am attaching the excel file.
Some days i may not have any data for ClockIn and ClockOut.
We need to take account of that.
Also the cells for Clockin and Clockout may be blank instead of zero.

(Hi Nilem: This time please allow the employee name be number between 1 and 99)

Please have a look and let me know.

Thanks
Riz

Hello Riz,

This macro works on the data you posted in your first post. The values have been changed to match with your requirements in post #9.

There are 2 macros. The first macro compiles a list of unique employee names. A name must be at least 3 alpha characters. Anything else is ignored. This list is then copied to the worksheet.

The second macro then sorts the names in ascending alphabetical order.

Try this out and let me know the results.

Please Login or Register  to view this content.