Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manual AutoFilter - Vlookup, Index, Match, Array???
I need some help with a spreadsheet that is setup as follows... "SummaryofChanges" Sheet - Contains data organized by column headings and each row represents a unique person. Some values can be identical between rows, but the Social Security Number is column A will always be unique. Column B contains either "new hire", "existing employee", or "terminated". All other columns contain data about the employee. The remaining sheets in the workbook are labelled according to "new", "existing", and "term". "New" Sheet - should list employee records that show "new hire" "Existing" Sheet - should list employee records that show "existing employee" "Term" Sheet - should list employee records that show "terminated" I need help getting this information to auto-populate on each of these sheets. Although autofilter would normally be used is not a reasonable option because of additions, subtractions and general formating changes that will be done to each employee record once the are move to each sheet. Is there a way to do this without autofilter?? THANK YOU! -- TEAM ------------------------------------------------------------------------ TEAM's Profile: http://www.excelforum.com/member.php...o&userid=22810 View this thread: http://www.excelforum.com/showthread...hreadid=541860 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manual AutoFilter - Vlookup, Index, Match, Array???
Here's one play which automates it using non-array formulas ..
A sample implementation is available at: http://www.savefile.com/files/8936561 Auto-Extract Data to Own Sheet by Category.xls In sheet: SummaryofChanges (the "master" sheet) Assume data in cols A to E, data in row2 down, with the key col = col B (say "Categories": New Hire, Existing Employee or Terminated) Using empty cols to the right, List the 3 categories across in K1:M1 Put in K2: =IF($B2=K$1,ROW(),"") Copy K2 across to M2, fill down to say, M20 to cover the max expected extent of source data 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 taken from a post by Harlan Grove.) In a new sheet named: New Hire With the same col headers pasted into A1:E1 Put in A2: =IF(ISERROR(SMALL(OFFSET(SummaryofChanges!$J:$J,,M ATCH(WSN,SummaryofChanges!$K$1:$M$1,0)),ROWS($A$1: A1))),"",INDEX(SummaryofChanges!A:A,MATCH(SMALL(OF FSET(SummaryofChanges!$J:$J,,MATCH(WSN,SummaryofCh anges!$K$1:$M$1,0)),ROWS($A$1:A1)),OFFSET(Summaryo fChanges!$J:$J,,MATCH(WSN,SummaryofChanges!$K$1:$M $1,0)),0))) Copy A2 across to E2, fill down to say, E10 (copy down by the smallest possible range sufficient to cover the max expected extent for any category. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient) Cols A to E will return only the lines for cat: New Hire from "SummaryofChanges", with all lines neatly bunched at the top Now just make a copy of the sheet: New Hire, rename it as the next cat: Existing Employee, and we'd get the results for that category Repeat the copy rename sheet process to get the last of the 3 category sheets (Terminated). Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TEAM" wrote: I need some help with a spreadsheet that is setup as follows... "SummaryofChanges" Sheet - Contains data organized by column headings and each row represents a unique person. Some values can be identical between rows, but the Social Security Number is column A will always be unique. Column B contains either "new hire", "existing employee", or "terminated". All other columns contain data about the employee. The remaining sheets in the workbook are labelled according to "new", "existing", and "term". "New" Sheet - should list employee records that show "new hire" "Existing" Sheet - should list employee records that show "existing employee" "Term" Sheet - should list employee records that show "terminated" I need help getting this information to auto-populate on each of these sheets. Although autofilter would normally be used is not a reasonable option because of additions, subtractions and general formating changes that will be done to each employee record once the are move to each sheet. Is there a way to do this without autofilter?? THANK YOU! -- TEAM ------------------------------------------------------------------------ TEAM's Profile: http://www.excelforum.com/member.php...o&userid=22810 View this thread: http://www.excelforum.com/showthread...hreadid=541860 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manual AutoFilter - Vlookup, Index, Match, Array???
Ensure that the names of the 3 sheets: New Hire, Existing Employee, Terminated
match exactly* with the 3 categories listed in col B in SummaryofChanges *except for case (Watch out for any inconsistencies, typos, extraneous white spaces, etc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Manual AutoFilter - Vlookup, Index, Match, Array???
A sample implementation is available at:
http://www.savefile.com/files/8936561 Auto-Extract Data to Own Sheet by Category.xls Have corrected some discrepancies in the earlier sample file (just detected, apologies). Revised sample uploaded at the same link. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
VLookup or Index Match ? | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |