Manual AutoFilter - Vlookup, Index, Match, Array???

[TEAM]

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!

 

[Don Guillett]

You could but why not just have a macro that does use autofilter for each
group and auto copies to the separate sheets, overwriting the old info each
time..

 

[Guest]

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,,MATCH(WSN,SummaryofChanges!$K$1:$M$1,0)),ROWS($A$1:A1))),"",INDEX(SummaryofChanges!A:A,MATCH(SMALL(OFFSET(SummaryofChanges!$J:$J,,MATCH(WSN,SummaryofChanges!$K$1:$M$1,0)),ROWS($A$1:A1)),OFFSET(SummaryofChanges!$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 ..

---

 

[Guest]

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)

---

 

[Guest]

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.

---