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


--
solar_eclipse2
------------------------------------------------------------------------
solar_eclipse2's Profile: http://www.excelforum.com/member.php...o&userid=24057
View this thread: http://www.excelforum.com/showthread...hreadid=376599

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.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=376599

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
DataImport External DataNew 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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=376599

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.


--
solar_eclipse2
------------------------------------------------------------------------
solar_eclipse2's Profile: http://www.excelforum.com/member.php...o&userid=24057
View this thread: http://www.excelforum.com/showthread...hreadid=376599

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=376599

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


--
solar_eclipse2
------------------------------------------------------------------------
solar_eclipse2's Profile: http://www.excelforum.com/member.php...o&userid=24057
View this thread: http://www.excelforum.com/showthread...hreadid=376599