Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match values in two different columns
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. -- thomasNg ------------------------------------------------------------------------ thomasNg's Profile: http://www.excelforum.com/member.php...fo&userid=8375 View this thread: http://www.excelforum.com/showthread...hreadid=487834 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match values in two different columns
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 thomasNg wrote: 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. -- thomasNg ------------------------------------------------------------------------ thomasNg's Profile: http://www.excelforum.com/member.php...fo&userid=8375 View this thread: http://www.excelforum.com/showthread...hreadid=487834 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match values in two different columns
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,M aster!$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). -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "thomasNg" wrote in message ... 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. -- thomasNg ------------------------------------------------------------------------ thomasNg's Profile: http://www.excelforum.com/member.php...fo&userid=8375 View this thread: http://www.excelforum.com/showthread...hreadid=487834 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Distributing values from rows to columns for Access import | Excel Worksheet Functions | |||
Finding a match in several columns | Excel Worksheet Functions | |||
MATCH function - 2 columns w/ SIMILAR, not EXACT data | Excel Worksheet Functions | |||
Totalling values across columns | Excel Worksheet Functions | |||
Creating a chart with values from two columns | Charts and Charting in Excel |