match values in two different columns

[thomasNg]

dear forum,

help please.
i have 2 columns in sheet 1: col A is employee ID, col B is Dept name.
i want to search col B, match it with the employee ID in col A.
i have several other sheets so that each sheet shows only the employee
IDs for one particular dept.

so far i have used a simple if(dept="marketing",a1,"--"). i am not too
sure how to use vlookup in this case. also i dont want to skip rows.

this is what i am getting with my formula
sheet 1 sheet 2
sheet 3
1012 Acct --
1012 acct
1064 Marketing 1064 marketing
--
2066 Admin --
--
5066 Acct --
5066 acct
6654 Marketing 6654 marketing
--

sorry this post is longwinded, but i thought i'd give as much b/ground
info as possible.

thanx

thomas.

 

[DOR]

Thomas

If this is a one-time requirement, look further down in this post for
simple approaches. If it is an on-going requirement that needs to be
done automatically do the following:

Put the name of the department in cell A1 of its own sheet. In cell A2
of that sheet put:

=IF(COUNTIF(sheet1!$B$2:$B$15,$A$1)>=ROW($A1:$A1),INDEX(sheet1!$A$2:$A$15,SMALL(IF(sheet1!$B$2:$B$15=$A$1,ROW(INDIRECT("1:"&ROWS(sheet1!B2:B15))),""),ROW($A1:$A1))),"")

Enter this formula as an array formula, that is, instead of pressing
Enter, press Ctl-Shift-Enter at the same time. This will automatically
put curly brackets around the formula indicating that it is an array
formula.

Then copy/drag this formula down as far as you need to to cover all the
employees in that department.

Do this for each department on different sheets.

Alternatively, if you only want to do this one time, use AutoFilter on
the column B to select each department and then copy column A and paste
special values into the appropriate sheet.

Or, if all else fails and this is a one-time effort, use the formula
you have been using, then sort column A and delete the blank cells, and
copy/paste special the employeeIDs as values.

Hope this helps

Declan O'R

 

[Max]

One play using non-array formulae which would "auto-extract" the employee
ids into each dept's sheet from a master list ..

A sample implementation is available at:
http://cjoint.com/?lydMDFJAhn
AutoSortEmployee_InTo_Dept_SheetName.xls

Assume the master list is in sheet: Master
in cols A to B, headers in row1, data from row2 down

Using empty cols to the right of the data, say cols K onwards
List the Dept names in K1, L1 across: Mktg, Admin, Acct, etc
Put in K2: =IF($B2=K$1,ROW(),"")
Copy K2 across to M2, fill down to say M20,
to cover the max expected data in the master list

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan)

In a new sheet named: Mktg
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$1:$IV$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$
1:$IV$1,0)),0)))

Copy A2 across to B2, fill down to B20
(cover the same range size as was done in "Master"' cols K, L)

Cols A to B will return only the lines for the Dept: Mktg from "Master",
with all lines neatly bunched at the top

Now, just make a copy of the sheet: Mktg, rename it as: Admin, and you'd get
the results for Admin. Repeat the sheet copy & rename as required to
extract the lists for the rest of the depts (a one-time effort).