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

[solar_eclipse2]

Ok, this is what I have...

I have 2 excel files each are half of a phonebook (with names an
addresses) and 9 files of of just phone numbers sorted by prefix. Al
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 phon
numbers that are sorted by prefix and flag or highlight th
duplicates.


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

 

[duane]

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.

 

[Ron Coderre]

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

 

[solar_eclipse2]

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.

 

[Ron Coderre]

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

 

[solar_eclipse2]

Yes, I would very much like to learn how to do this. I would greatly
appreciate your help.