Merging Workbooks

[LOUBOYBEAR]

Hi
I have 2 data lists:
1 for staff (employee numbers)
1 for jobs (position numbers)

I need to basically merge the two sheets together.
I have tried to do this with a vlookup looking at the POSITION number and finding the person with that position number in the staff list; but if more than one person has the same position number it only returns the first match.
I have also tried to do this by using the EMPLOYEE number as the lookup, which works, except I miss out on reporting on employee numbers where there is no staff member (ie vacant position).

I need to create a single w/s from the 2 reports bringing in all of the positions, putting the staff members name next to it when its occupied and "vacant" when it vacant.
Any suggestions?

 

[mgirvin]

Is it something like this:

Employee Table:

Excel Workbook
	A	B	C
1	Employee ID	Employee	Position #
2	1	Jo	Accoutning 1
3	2	Phil	Accoutning 3
4	3	Sue	Finance 1
5	4	Hal	Finance 3
6	5	Tom W	Human Resources
7	6	Ral	Operations 2
8	7	Ralf	Operations 3
9	8	Ghor	Operations 4
10	9	Pam	Admin 1
11	10	Fin	Admin 3
12	11	Sioux	Accoutning 1
13	12	Chin	Accoutning 3
14	13	June	Finance 1
15	14	Sheliadawn	Finance 3
16	15	Sue R	Human Resources
17	16	Lim	Operations 2
18	17	Dither	Admin 1
19	18	Tom	Accoutning 3
20	19	Gigi	Operations 2
Sheet1

Excel 2010

Position table with formulas in green:

Excel Workbook
	E	F	G	H	I	J
1	Position #	Count	Employee	Employee	Employee	Employee
2	Accoutning 1	2	Jo	Sioux	.	.
3	Accoutning 2	0	.	.	.	.
4	Accoutning 3	3	Phil	Chin	Tom	.
5	Finance 1	2	Sue	June	.	.
6	Finance 2	0	.	.	.	.
7	Finance 3	2	Hal	Sheliadawn	.	.
8	Human Resources	2	Tom W	Sue R	.	.
9	Operations 1	0	.	.	.	.
10	Operations 2	3	Ral	Lim	Gigi	.
11	Operations 3	1	Ralf	.	.	.
12	Operations 4	1	Ghor	.	.	.
13	Operations 5	0	.	.	.	.
14	Admin 1	2	Pam	Dither	.	.
15	Admin 2	0	.	.	.	.
16	Admin 3	1	Fin	.	.	.
Sheet1

Excel 2010

Cell F2 and copy down:

Excel Workbook
	F
2	2
...

Cell Formulas
Range	Formula
F2	=COUNTIF($C$2:$C$20,E2)

Cell G2 and copy through range G2:J16:

Excel Workbook
	G
2	Jo
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[LOUBOYBEAR]

Hi Mike, thanks for your reply.
What I need to create is a single row for each position number which contains all of the info about the position, and then if the position is occupied all of the information about the person occupying the position. If 2 people occupy the same position, obvioulsy the position details are the same, but their personal information is different.

The 2 reports where the position and employee information is picked up from are able to be changed if needed, but the linking field is obvioulsy the position number.

I am sorry but I not sure how to paste a picture and I am at work an unable to download anything onto the work computer. I am happy to send the s/s if it helps.
thanks again, I appreciate your time.

Sam Naughtin

 

[mgirvin]

I do not understand what you are trying to do. I did not understand in your first post and I just tried to make a guess at what you wanted. Maybe you could post a sample data set of how the tables look before and how they should look after? Give at least one example and what the expected result should be.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>