Finding multiple values in excel, then sending as "mail merge"
 

I am an insurance underwriter. I have a monthly
spreadsheet of well over 10,000 lines. It lists all the
agencies our company has and each policy each agency has.
Each agency is assigned a code number. Each policy has a
number.

I have a two fold problem I do not know how to do:

1) Each month find my 20-something agencies based on code
number (and the accompanying many hundreds of lines of
policies), discard all the other lines, then

2) select certain cells from each line to "mail merge"
into another Excel sheet (This second excel sheet
receiving the data can be converted to a Word document if
needed).

Manually doing this every month by cut & paste is too long.
I can highlight & erase all those agencies that are not
mine, but this is too slow and only is part 1.

I'm a "toddler" in Excel, know enough to ask for help, but
do not know the terminology well. Still learning.
Please use low-tech speak!
Many Thanks
Tom

Finding multiple values in excel, then sending as "mail merge"
 

Look at help on Data=Filter=Autofilter (filter all rows that don't fit
the criteria, then delete all rows (only the visible rows will be delete).

You migh have to put in a dummy column to use as the filter criteria. This
dummy column would have a formula that determines whether that row should be
deleted or not. Once you enter the formula in row2, then drag fill it down
the column and apply the Autofilter.

another approach is to use Data=Filter=Advanced filter. this can actually
filter the data you want to another location and can accept much more
complex criteria.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
I am an insurance underwriter. I have a monthly
spreadsheet of well over 10,000 lines. It lists all the
agencies our company has and each policy each agency has.
Each agency is assigned a code number. Each policy has a
number.

I have a two fold problem I do not know how to do:

1) Each month find my 20-something agencies based on code
number (and the accompanying many hundreds of lines of
policies), discard all the other lines, then

2) select certain cells from each line to "mail merge"
into another Excel sheet (This second excel sheet
receiving the data can be converted to a Word document if
needed).

Manually doing this every month by cut & paste is too long.
I can highlight & erase all those agencies that are not
mine, but this is too slow and only is part 1.

I'm a "toddler" in Excel, know enough to ask for help, but
do not know the terminology well. Still learning.
Please use low-tech speak!
Many Thanks
Tom

Finding multiple values in excel, then sending as "mail merge"
 

Hi Tom,
There is no need to cut and paste (now they tell me).
You can use a Filter, only the filtered items will be seen by mail merge,
and for that matter by copy or cut as in cut and paste.

Some hints you might also include columns with x's for
has been sent, should be sent. So that you can
select the ones you want to go with a particular letter format.
Then also select on your typical other fields you mentioned:
agencies

I have a page on mail merge it is written for printing labels,
but printing letters is very similar. You can look at one of
the linked pages for people who deal more with Word after
getting through the essentials of using Excel as your database.
They one thing they always miss (perhaps not anymore) is that
you want to have the first worksheet tab be your list. (don't forget
to filter).

To get you started with AutoFilter
Select All (Ctrl+A), Filter, AutoFilter (by clicking you turn on the checkmark)
you will see dropdown from which you can select. 20000 may
-- and just so you don't get paranoid and go back to your backup,
to remove filtering: Filter, AutoFilter (by clicking you turn off)

If you want more information on filters you can go through the text
and pictures at Debra Dalgleish's website
http://www.contextures.com
I think, she also has Mail Merge pages that are more from a word perspective.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom" wrote in message ...
I am an insurance underwriter. I have a monthly
spreadsheet of well over 10,000 lines. It lists all the
agencies our company has and each policy each agency has.
Each agency is assigned a code number. Each policy has a
number.

I have a two fold problem I do not know how to do:

1) Each month find my 20-something agencies based on code
number (and the accompanying many hundreds of lines of
policies), discard all the other lines, then

2) select certain cells from each line to "mail merge"
into another Excel sheet (This second excel sheet
receiving the data can be converted to a Word document if
needed).

Manually doing this every month by cut & paste is too long.
I can highlight & erase all those agencies that are not
mine, but this is too slow and only is part 1.

I'm a "toddler" in Excel, know enough to ask for help, but
do not know the terminology well. Still learning.
Please use low-tech speak!
Many Thanks
Tom