non-contiguous columns

[Scruffy77]

Hello,

I have searched Excel Help and forums on here and I haven't been able to find an answer similar to what I require.
Is there a formula that I can use to transfer the employees dept (Column F) to Column H (dept)

Whereas I have used the Advanced Filter to transfer the unique Employee names to Column G, but I require the respective employee's department in Column H.

I believe a match formula may do, but I don't think they work with non-contiguous columns
Any ideas?
Thank you in advance!

DRAFT FOR INTERNET - JULY 3.xls
	B	C	D	E	F	G	H	I
2	Name	Report Date	TRC	Qty (Hrs)	DEPT	NAME	DEPT	OCCURENCES
3	EMPLOYEE1	15/05/2007	SICK	7.00	SALES	EMPLOYEE1	SALES
4	EMPLOYEE1	14/05/2007	SICK	3.50	SALES	EMPLOYEE2	MKTING
5	EMPLOYEE1	28/02/2007	SICK	7.00	SALES	EMPLOYEE3	HR
6	EMPLOYEE1	27/02/2007	UNPEM	7.00	SALES	EMPLOYEE4
7	EMPLOYEE2	03/01/2007	SICK	7.00	MKTING	EMPLOYEE5
8	EMPLOYEE3	22/03/2007	SICK	8.00	HR	EMPLOYEE6
9	EMPLOYEE3	21/03/2007	SICK	8.00	HR
10	EMPLOYEE4	30/05/2007	SICK	8.00	OPERATIONS
11	EMPLOYEE4	29/05/2007	SICK	8.00	OPERATIONS
12	EMPLOYEE4	29/03/2007	SICK	8.00	OPERATIONS
13	EMPLOYEE4	10/03/2007	SICK	8.00	OPERATIONS
14	EMPLOYEE4	09/03/2007	SICK	8.00	OPERATIONS
15	EMPLOYEE4	13/02/2007	SICKA	4.00	OPERATIONS
16	EMPLOYEE4	17/01/2007	SICK	8.00	OPERATIONS
17	EMPLOYEE4	16/01/2007	SICK	8.00	OPERATIONS
18	EMPLOYEE4	15/01/2007	SICK	8.00	OPERATIONS
19	EMPLOYEE4	13/01/2007	SICK	2.83	OPERATIONS
20	EMPLOYEE5	19/04/2007	SICK	8.00	ADMIN
21	EMPLOYEE5	18/04/2007	SICK	8.00	ADMIN
22	EMPLOYEE5	17/04/2007	SICK	4.50	ADMIN
23	EMPLOYEE5	30/03/2007	SICK	8.00	ADMIN
FULL QUERY DEPTS

 

[Scruffy77]

Sorry, I should add that there are approx 2000 lines with many employees from many different departments

 

[Jonmo1]

In H3 and filled down...

=Vlookup(G3,B:F,5,False)

 

[Jonmo1]

Now this is just a guess, but Next, You probably are wanting to count how many SICK there are for each employee?

in I3 and filled down..

=SUMPRODUCT((A$1:A$10000=G3)*(D$1:D$10000="SICK"))

 

[Norie]

Looks to me to be good candidate for a pivot table.

 

[Scruffy77]

Hello,

Thanks for the vlookup, it works great! I have to admit, I'm not the greatest when it comes to the more advance functions (they are advanced to me ok!
Close assumption with the amount it's a litte more complicated than that. I had to count occurences and Andrew Poulsom was kind enough to provide the following whopper of a formula:
=SUMPRODUCT(--(B$3:B$2232=H3),--IF((B$4:B$2233=H3),((C$3:C$2232-C$4:C$2233)>1),1))

(more columns involved then the intial sample above)
Thank you all for your assistance. I aspire to be as knowledgable in Excel as the rest of you.

Thanks again!

 

[Scruffy77]

Hi jonmo,

Now that I have been looking at the data I believe I will need to know the number of times an employee has a date recorded with a TRC similar to your formula, but it needs to cover all TRC's (not just SICK) and sometimes 2 TRC's are recorded on the same date so it will need to ignore the duplicate dates.

Essentially, count how many times there is a date (with a TRC) recorded for an employee while ignoring the duplicate dates.
I have tried trying to tweak your formula from above without much luck.

For example;
Employee 1 has four TRC's recorded, so I would like to have a summary that states "Employee Name" & "Number of Days Recorded" (in this example 4)

Any chance if this is possible?
Thank you in advance