I need a formula to Vlookup unique values....

Hi,

I hope you mean by tabs just sheets.

I would select on every sheet ~200 entries in turn and give them a name. I would name the employee entries on the monday sheet "MonWorkers", and so on.

I would create a columnar list of all employess and name it EMPLOYEES on your "6th tab" or on another sheet.

I would select on sheet 6 first N cells from say A2 on, where N is the total number of all employees (that is, the number of employees in EMPLOYEES). Then array-enter the following formula in A2:

=IF(NOT(ISNA(VLOOKUP(EMPLOYEES,MonWorkers,1,0))),EMPLOYEES,"")

This will give you the list of employees that worked on Monday. I would repeat the same proc for the rest of workdays in column B and so on.

I believe the results so obtained can be used for further computations.

Good luck.

Aladin

Hi GLITZ

It sounds like an ideal job for a Pivot Table, using "Multiple Consonlidated ranges". Worth a look at least I think.

Dave
OzGrid Business Applications

Is there a way to create the list of employees in a single column on the sixth sheet?

: I'm looking for a weekly summary of employee data. I have 5 tabs in a workbook each tab represents a weekday. Each day has different employee's working and lists them by employee number in the A column. All important lookupable info is to the right of the employee number for each day. There are never more than 200 employees listed in a single day. : I want to have a 6th tab that will create a list of employees that worked and put each persons number in the list only once. From there I will do individual day lookups and compile certain info. : : Any tips? : Thank you Hi, I hope you mean by tabs just sheets. I would select on every sheet ~200 entries in turn and give them a name. I would name the employee entries on the monday sheet "MonWorkers", and so on. I would create a columnar list of all employess and name it EMPLOYEES on your "6th tab" or on another sheet. I would select on sheet 6 first N cells from say A2 on, where N is the total number of all employees (that is, the number of employees in EMPLOYEES). Then array-enter the following formula in A2: =IF(NOT(ISNA(VLOOKUP(EMPLOYEES,MonWorkers,1,0))),EMPLOYEES,"") This will give you the list of employees that worked on Monday. I would repeat the same proc for the rest of workdays in column B and so on. I believe the results so obtained can be used for further computations. Good luck. Aladin

I see I failed to mention the very reason why the formula must be array-entered. That is, I should have said: put the formula in A2 (as one ordinarily does) and select N cells from A2 on, go to the formula bar and array-enter the formula [N = total number of different employees]. This produces all the workers of monday in one go. Now, you can put the same formula in the cell after the last cell (of the resulting monday-list) again and replace MonWorkers by TueWorkers, select again N cells and array-enter the edited formula from the formula bar. Repeat this proc for the rest of the workdays. You have now everything in a single column on the 6th sheet.

Aladin

:

Thanks for working with me Aladin,
Tabs=Sheets

Perhaps I was unclear in what I was trying to do.
Mon (tab 1 column A)Employees 1,2,3,4,5 worked
Tue (tab 2 column A)Employees 3,4,5,6,7 worked
Wed (tab 3 column A)Employees 7,8,9,10 worked
Thur(tab 4 column A)Employees 9,10,11,12 worked
Fri (tab 5 column A)Employees 10,11,12,13 worked

On the 6th tab in the A Column (as a summary)
The A column should show the following employee's:
1,2,3,4,5,6,7,8,9,10,11,12,13
This is a list of everyone who worked during the week.....

GLITZ

I hope you will not mind the solution below.

I suggested in the previous post that you determine in 5 blocks of N cells the employees that worked on different weekdays in column A. Select these 5 blocks of N cells and name this selection WeekWorkers. Put the following formula in B2

=IF(NOT(ISNA(VLOOKUP(EMPLOYEES,WeekWorkers,1,0))),EMPLOYEES,"") then select N cells from B2 on, go to the formula bar, and array-enter the above formula. This gives you a list of everyone who worked during the week.

Aladin

:

I can't seem to make it work....every the first method you suggested. My array returns nothing but zero's.

I'm still trying....

GLITZ

Hi GLITZ,

If you drop a line, I can send you the workbook containing the machinery.

Aladin