+ Reply to Thread
Results 1 to 4 of 4

Paring list for mail merge from second list - Index Match?

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    Michigan
    MS-Off Ver
    Office 2010
    Posts
    21

    Paring list for mail merge from second list - Index Match?

    I need to get a monthly mailing out to a list of top sales reps as described below. What is my best way to merge or narrow these to demand the least amount of manual intervention? At the end, I'll tell what I've tried so far.

    I have two lists:
    1) Master list of level 1 recipients as of previous month (not updated daily). This list contains addresses, first and last name in one column, and their level 2 manager in a field with the managers first and last name. This list will be updated with missing folks from the second list (manually and I think I have that solved) and the will be pared down to only those that exist then on both lists.
    2) List of monthly top Lvl1 reps according to sales volume. That list includes the Lvl1Name, related Lvl2Name, and a few other unique items like sales volume. This list only includes those who should receive the mailing. This list may be 3-10% of the entire master list in a given month.

    Each month I need to narrow the Master list with the addresses to only those on the monthly sales list. The master list is formatted for a mail merge, so narrowing it would be easier (I think) than copying relevant addresses to the second list. Although, it may be possible to move those addresses and format the second list as necessary if that's far less headache.

    Here are the problems
    - There are no unique identifiers. So, there are multiple Lvl1 reps with the same name on both lists. There could also be two Lvl1s with the same name under the same Lvl2.
    - The the lists come from different databases and don't always have the same spellings (seriously). For instance, the monthly list may truncate last names and adds middle initials. It also sometimes lists first names as initials only. These lines take manual intervention now and I assume will have to in the future. I've handled the middle initials.
    - There could be 5 Joe Smiths on the master list and 3 on the TopSales list.


    CountIf
    Helpful for finding those missing and/or mispelled. I run it on the Top list and any 0s show a problem that needs manual intervention.


    Match
    I used Match to compare BOTH the Lvl1 and Lvl2 names on the same row, which gives me good results that I may have to tweak if I can get it to run quickly. It takes over an hour to run, so I only did that once. It returns a result that shows the row number of the same pair between the two sheets. Slick.

    But, not only does it run slowly, but I think it may return a false negative it it sees the same Lvl1 name, but the Lvl2 name is wrong. In this case, would it continue to find the next same Lvl1 name until it finds a matched pair or reaches the end or will it stop?


    So, could I use some form of Index and Match to make this work faster? If I ran it by Lvl2 first, then Lvl1, would that be better? I'm going to cut my lists to about 1/3 or so and do more testing, but could really use some help finding the right direction.

    Here's what I'm playing with so far in the Master list:

    =MATCH(Q2&A2,'Top10'!D:D&'Top10'!A:A,0)

    Where:
    Q is the Lvl1 full name column
    A is the Lvl2 full name column
    Top10 D column is its Lvl1 full name column
    Top10 A column is its Lvl2 full name column

    Thanks.

  2. #2
    Registered User
    Join Date
    05-29-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Paring list for mail merge from second list - Index Match?

    With all the variables you are dealing with (same names, different spellings, etc..) I don't know that there is a perfect way to do it. However, one thing that may help:

    I personally hate index-match. As you mentioned, it takes forever for anything to work. I prefer to use CONCATENATE and get the same results. Concatenate combines 2 cell values as 1 and then I just do a vlookup on the new combined value. Works much faster:

    Example:

    I deal with the same item that can be in multiple stores. I may want to pull OHs, for multiple titles at multiple stores. I can either do an index match to find everywhere that both the STORE NUMBER and UPC match or I can concatenate them.

    Store UPC Concatenate
    9602 000000000001 9602000000000001
    9603 000000000001 9603000000000001
    9604 000000000001 9604000000000001

    I can now search for that ONE combination via Vlookup instead of index match b/c you have created a unique identifier. Dunno if that helps at all but I figured I would throw it out there.
    Last edited by jlax34; 11-30-2012 at 06:14 PM.

  3. #3
    Registered User
    Join Date
    11-30-2012
    Location
    Michigan
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Paring list for mail merge from second list - Index Match?

    I'm going to give that a run. I'll test it and see if I can come up with a situation where it can't work, but I think you solved the problem with pretty much the simplest solution. Sometimes a hammer is the right tool for the wrong job. I'm optimistic.

    I guess it could get messy if there are two same names Lvl1s under a Lvl2 and they both hit a top spot in a month. Maybe I'll use CountIf and sort by all non-1s after concatenating.

    Guess I need to finally get my head around Vlookup as well....

  4. #4
    Registered User
    Join Date
    11-30-2012
    Location
    Michigan
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Paring list for mail merge from second list - Index Match?

    I think that worked, now to get the rest of this to work. I think a new thread is in order.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1