non-contiguous columns

Scruffy77

New Member
Joined
Feb 26, 2004
Messages
29
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
BCDEFGHI
2NameReport DateTRCQty (Hrs)DEPTNAMEDEPTOCCURENCES
3EMPLOYEE115/05/2007SICK7.00SALESEMPLOYEE1SALES
4EMPLOYEE114/05/2007SICK3.50SALESEMPLOYEE2MKTING
5EMPLOYEE128/02/2007SICK7.00SALESEMPLOYEE3HR
6EMPLOYEE127/02/2007UNPEM7.00SALESEMPLOYEE4
7EMPLOYEE203/01/2007SICK7.00MKTINGEMPLOYEE5
8EMPLOYEE322/03/2007SICK8.00HREMPLOYEE6
9EMPLOYEE321/03/2007SICK8.00HR
10EMPLOYEE430/05/2007SICK8.00OPERATIONS
11EMPLOYEE429/05/2007SICK8.00OPERATIONS
12EMPLOYEE429/03/2007SICK8.00OPERATIONS
13EMPLOYEE410/03/2007SICK8.00OPERATIONS
14EMPLOYEE409/03/2007SICK8.00OPERATIONS
15EMPLOYEE413/02/2007SICKA4.00OPERATIONS
16EMPLOYEE417/01/2007SICK8.00OPERATIONS
17EMPLOYEE416/01/2007SICK8.00OPERATIONS
18EMPLOYEE415/01/2007SICK8.00OPERATIONS
19EMPLOYEE413/01/2007SICK2.83OPERATIONS
20EMPLOYEE519/04/2007SICK8.00ADMIN
21EMPLOYEE518/04/2007SICK8.00ADMIN
22EMPLOYEE517/04/2007SICK4.50ADMIN
23EMPLOYEE530/03/2007SICK8.00ADMIN
FULL QUERY DEPTS
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Scruffy77

New Member
Joined
Feb 26, 2004
Messages
29
Sorry, I should add that there are approx 2000 lines with many employees from many different departments
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Looks to me to be good candidate for a pivot table.
 

Scruffy77

New Member
Joined
Feb 26, 2004
Messages
29
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

New Member
Joined
Feb 26, 2004
Messages
29
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
 

Forum statistics

Threads
1,181,364
Messages
5,929,544
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top