+ Reply to Thread
Results 1 to 6 of 6

I know what I need but dont know how to do it

  1. #1
    Registered User
    Join Date
    06-05-2005
    Posts
    3

    I know what I need but dont know how to do it

    Ok, this is what I have...

    I have 2 excel files each are half of a phonebook (with names and addresses) and 9 files of of just phone numbers sorted by prefix. All of the hyphens have been removed from the phone numbers.


    What I need...
    I need to take the phonebook files and compare them with the phone numbers that are sorted by prefix and flag or highlight the duplicates.


    Can anyone help me? I really have no idea what to do

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    if you are ok with a macro then it is pretty straight forward to step thru the 9 worksheets one by one looking for a number in the original workbook.

    let me know if you want to go that way.
    not a professional, just trying to assist.....

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    You might consider this approach if:
    -All the data is in Excel files
    -You are handy with MS Query
    -Don't mind playing with some SQL code

    So...Just for demo purposes, I put 3 lists in 3 Excel files:
    MainList1.xls contains a range named "rngListData" that contains a short list of items I want to find in other files

    PhoneList1.xls and PhoneList2.xls contain lists of phone numbers (in rngData1 and rngData2, respectively) that I want to compare to the MainList1 file.

    My approach is to use MS Query (via Excel) to append the PhoneList2.xls to PhoneList1.xls and see if that combined list has any matches on MainList1.xls

    Here we go . . . .

    In a new workbook I selected
    Data>Import External Data>New Database Query
    Data Source: Excel Files (I navigated to the PhoneList1.xls and selected the
    rngData1 range name)

    Then I selected all columns, accepted all defaults, and opted for "View data
    or edit query in Microsoft Query".

    I clicked the [SQL] button to view the SQL code and edited it to be the
    following:

    SELECT Combo.PhoneNum
    FROM (
    SELECT List1.PhoneNum FROM `C:\Analysis\PhoneList1`.rngData1 List1
    UNION ALL
    SELECT List2.PhoneNum FROM `C:\Analysis\PhoneList2`.rngData2 List2
    ) Combo, `C:\Analysis\MainList1`.rngListData MainList
    WHERE MainList.PhoneNum = Combo.PhoneNum

    Then click the Return Data button in MS Query.

    Doing that returned all items on PhoneList1.xls and PhoneList2.xls that had matching phone numbers on MainList1.xls

    Note: That method will work for pretty much any number of files that you need appended to each other. Not sure how much heavy lifting MS Query can do (I use MS Access for the bigger stuff).

    Is that something you might be able to work with?

    Regards,
    Ron

  4. #4
    Registered User
    Join Date
    06-05-2005
    Posts
    3
    Wow, that sounds like some heavy stuff. I am not familiar with SQL or programming in general.

    Say my files are phonebook a-g.xls and phonebook h-z.xls and all the others are prefix 238.xls and whatever other prefixes are the steps that Ron Coderre layed out going to find the numbers from the phonebook that have matches in the prefix files and shoot them into a new file for me? I'm sorry to be annoying but if someone could just break it down a little more for me that would be great.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Yes... MS Query would
    1)Combine a-g.xls and h-z.xls into one "table".
    2)Combine 238.xls (and whatever other prefix files you have) into another "table".
    3)Find all items that are on both tables.
    and
    4)Put that list into the new workbook.

    If that is something you want to learn to do, let me know.

    Ron

  6. #6
    Registered User
    Join Date
    06-05-2005
    Posts
    3
    Yes, I would very much like to learn how to do this. I would greatly appreciate your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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