I have a worksheet that has the following information:
Initials of an employee, new loans signed up and all the relevant
information i.e.

DJC 12345 50,000.00 MORTGAGE
DJC 22456 10,000.00 1999 Chevrolet
AAB 66442 25,000.00 OPERATING LOAN
AAB 60077 9,000.00 UNSECURED

I have a separate worksheet that I need to list all of the loans under each
specific employee. What formula would I use? I've tried LOOKUP but it will
only find the first set of initials and data.
--
sworr

datafilterautofilterfilter on the initial column

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sworr" wrote in message
...
I have a worksheet that has the following information:
Initials of an employee, new loans signed up and all the relevant
information i.e.

DJC 12345 50,000.00 MORTGAGE
DJC 22456 10,000.00 1999 Chevrolet
AAB 66442 25,000.00 OPERATING LOAN
AAB 60077 9,000.00 UNSECURED

I have a separate worksheet that I need to list all of the loans under
each
specific employee. What formula would I use? I've tried LOOKUP but it
will
only find the first set of initials and data.
--
sworr

Think this formulas play will accomplish what you're after

As updates are made in the master: WSN (the "parent" sheet),
lines will be auto-copied by emp ID to each emp ID sheet (the "child"
sheets)

Illustration in this sample, customized to your data as posted:
http://www.freefilehosting.net/download/3d0e4
AutoSlice lines by ID col to respective sheets.xls

One play which automates it using non-array formulas ..

In sheet: WS1 (the "master")
Assume source data as posted in cols A to D, data in row2 down,
with the key col = col A (Emp id)

List the emp IDs in K1 across: DJC, AAB, etc (can be in any order)
Put in K2: =IF($A2=K$1,ROW(),"")
Copy across as far as required, then fill down to cover the max expected
extent of 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 came from
a post by Harlan.

Then, in a new sheet named after one of the emp ids, eg: AAB
With the same col headers pasted into A1:D1

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

Copy A2 across to D2, fill down to say, D10 (copy down by the smallest
possible range sufficient to cover the max expected extent for any emp ID).
Here, I've assumed that 9 rows -- rows 2 to 10 -- is sufficient.

Cols A to C will return only the lines for the emp id: AAB from "WS1", with
all lines neatly bunched at the top

Now, just make a copy of the sheet: AAB, rename it as the next id: DJC, and
you'd get the results for that id. Repeat the copy rename sheet process to
get the rest of the id sheets (a one-time job) as required. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sworr" wrote:
I have a worksheet that has the following information:
Initials of an employee, new loans signed up and all the relevant
information i.e.

DJC 12345 50,000.00 MORTGAGE
DJC 22456 10,000.00 1999 Chevrolet
AAB 66442 25,000.00 OPERATING LOAN
AAB 60077 9,000.00 UNSECURED

I have a separate worksheet that I need to list all of the loans under each
specific employee. What formula would I use? I've tried LOOKUP but it will
only find the first set of initials and data.
--
sworr