Listing Data with Certain Qualifications

  • Thread starter Thread starter dnamertz
  • Start date Start date
D

dnamertz

I have a master list of data that is dozens of rows with the following
columns: Emplyee Name, Job Title, Salary, and Department Numbers. I then
have separate worksheets for each department number and on each worksheet, I
want to list the data for each employee who is in that department. I can use
an IF function to list the employees in that department, but that leaves
blank rows for the employees who are NOT in that department. How do I ONLY
list the data for employees in that department without having a bunch of
blank rows in between data.

Here is an example:

My master list of employees looks like this:
EMPLYEE TITLE SALARY DEPT 1 DEPT 2 DEPT 3
Bob S. Mgr $x,xxx 100%
Sue B. Director $x,xxx 100%
Allan K. Clerk $x,xxx 100%
Rob M. Clerk $x,xxx 50% 50%
Kate T. Mgr $x,xxx
100%
Rick. L. Clerk $x,xxx 100%

If I go into the worksheet for Dept 1 and use an IF function to include only
employee data for those on the master list who have over 0% in the "DEPT 1"
column, and copy that formula down, I get the following result including
blank rows between data:

Bob S. Mgr $x,xxx

Allan K. Clerk $x,xxx


Rick. L. Clerk $x,xxx

Notice how it lists emplyees in dept 1, but leaves blank rows for those not
in dpet 1. I would prefer it to list the data for these employees without
leaving blank rows for employees who don't qualify, so it should look like
this for dept 1:

Bob S. Mgr $x,xxx
Allan K. Clerk $x,xxx
Rick. L. Clerk $x,xxx

Can this be done?
 
Since my example in the original post didn't appear in the post the same way
it looked when I typed it, here is some clarification. In the example master
list, Bob S., Allan K., and Rick L. were in dept 1. The other employees were
in dept 2 or dept 3.
 
Here's one way which delivers it for you ..

Illustrated in this sample:
http://freefilehosting.net/download/3lh2e
Listing data by key col.xls

Source data as posted assumed in sheet: x
cols A to F, data from row2 down

In a new sheet for Dept 1,
In A2: =IF(x!D2>0,ROW(),"")
Col D in x is Dept 1's key col
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of
data in x. Minmize/hide col A. The required results will be listed in cols B
to D, with all lines neatly bunched at the top as desired.

Then make a copy of the sheet, name it for Dept 2
Just amend the formula in A2 to point to col E in x, viz in A2:
=IF(x!E2>0,ROW(),"")
Then copy A2 down, and you'd get the lines for Dept 2 listed in cols B to D

Repeat for Dept 3's sheet ...
Point to col F in x,
viz: in A2: =IF(x!F2>0,ROW(),""), copy A2 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
 
Max said:
Here's one way which delivers it for you ..

Illustrated in this sample:
http://freefilehosting.net/download/3lh2e
Listing data by key col.xls

Source data as posted assumed in sheet: x
cols A to F, data from row2 down

In a new sheet for Dept 1,
In A2: =IF(x!D2>0,ROW(),"")
Col D in x is Dept 1's key col
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of
data in x. Minmize/hide col A. The required results will be listed in cols B
to D, with all lines neatly bunched at the top as desired.

Then make a copy of the sheet, name it for Dept 2
Just amend the formula in A2 to point to col E in x, viz in A2:
=IF(x!E2>0,ROW(),"")
Then copy A2 down, and you'd get the lines for Dept 2 listed in cols B to D

Repeat for Dept 3's sheet ...
Point to col F in x,
viz: in A2: =IF(x!F2>0,ROW(),""), copy A2 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
Click to expand...



That's exactly what I was looking for. Thanks.
 
Back
Top