Please Help

  • Thread starter Thread starter Marcopolo
  • Start date Start date
M

Marcopolo

Hi,

I posted a thread a couple of days back. However, never really explaie
the problem very well.

I work in insurance. I receive a spreadsheet every week with a list o
about 500 policy holders. It contains various columns of informatio
(about 10 columns).

My department, however, only deals with 164 of these 500 polic
holders. As a result, I was wondering if anyone knew of a macro tha
could filter out these 164 policy holders from the spreadsheet an
either place them on a new sheet or filter out the policy holders tha
we didn't deal with.

At the moment all I have is a seperate sheet A1:A164 which contains th
list of policy holders we deal with.

Is this possible??! :confused
 
Please Help is a subject line guaranteed to get little response. Cull list
might be better
You fail to mention the criteria by which the 164 are selected out of 500?
________?
Try data>filter>autofilter>criteria you select>then copy/paste the visible
cells
 
How are the 164 records different from the rest? It
seems you'll need to identify something unique to those
records, then you can filter based on that unique factor.
 
If there is nothing unique about the 164 records you will need a macro.
This macro would look at each name or policy number in the 500 list and see
if it is listed in the 164 list. If it is, leave it alone. If it isn't,
delete it. Post back if this looks like what you want. HTH Otto
 
It's very easy to do what you want with Excel's Advanced Filter. It's best
explained with a simple example:

-In A1 put the heading "Name" and in B1 "Amt"
-Fill in some unique names for say 10 rows from A2 on down and some amounts
in B.
-Now in D1 put "Name" (must be the same as A1)
-In D2 to D4 put in any 3 names that appear in col A.

The A1 table is you "database" and the D1 list is your "criteria range".

-Select cell A1
-Do a Data, Filter, Advanced Filter
-Select "copy to another location"
-The "List range" should have your database range automatically
-For the criteria range select D1:D4
-For the Copy To range enter or point to F1
-Click OK

You should have a 2 column list starting at F1 of the 3 matching records
from your database. You can do exactly the same thing with your big
database. The criteria range can even be on another worksheet.
 
You could use a helper column that looks for a match between your list of
policies and the giant list's policy number column.

Insert that helper column (say column A) in the giant list.

Then use a formula like:

=ISNUMBER(MATCH(B2,[book1.xls]Sheet1!$A:$A,0))

In A2 and drag down (A1 has headers???).

Then either sort that data and copy the True's or apply Data|filter|autofilter
and filter on True's. Then copy the visble cells to a new worksheet/workbook.


If you really need a macro, you could record it when you do this stuff and
modify the heck out of it when you're happy with what it does.
 
Back
Top