Hi,

I am trying to match/cross reference (check by ZIP CODE) two extremely
large databases/spreadsheets in MS EXCEL. The vast majority of matches
will turn up negative (no match) and only like <5% of all the
rows/fields in my 31000+ excel sheet will actually match by zip to the
records contained in another sheet (actually this second sheet is a
large Seibel Sales database that I exported to Excel for easier
access/manipulation) So it would be very tedious and a time waster to
manually match the records by hand. My question would be, is there an
automated way in Excel (or using any number of Excel addons such as
ASAP Utilities etc) that would let the computer do the work for me??

Basically I have two large records, one that I exported from Siebel
online to Excel, and the second one is the original work Excel database
that I am to work on (ie find matches by zip code and mark them as
matched)

Is there a quick way to do this? The power of the computer should
really be put to use, but I just don't know /cant figure out how to go
about this.

Any ideas would be helpful, thanks!

BTW here is the screenshots and explainations if you don't know what I
am talking about:
http://www.freewebs.com/bxc2739/

-Bo


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=534995

Just to clarify (I'm not sure I explained it very good)

Imagine two sheets

Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be
worked on)
12345 54986 (mark as NO
MATCH)
84596 25746 (mark as NO
MATCH)
24578 12345 (mark as
MATCH!)
96328 15789 (mark as NO
MATCH)
45897 96328 (mark as
MATCH!)

The problem is Sheet #2 is not only five rows, it is like 30000+ rows,
and I
will have to do another 30000+ rows soon, so 60000+ to do by hand is
very slow indeed.

Also, 95% of all the rows in Sheet #2 will be a NO MATCH with no
matching zip codes to ANY of the zip codes of Sheet #1. So less than 5%
will actually be a match, but the thing is I have to check ALL of them
to actually know which ones match and which don't.

IS there a utility / macro or function in Excel that can quickly let me
do this the automated way?

Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=534995

2 solutions. You can test both or let the local gurus tell which is best

In sheet2 , column A (which appears to be free):
=IF(COUNTIF([Output.xls]Output!S:S,B2)0,"MATCH","NO MATCH")

or:
=IF(ISNA(MATCH(B2,[Output.xls]Output!S:S,;0),"NO MATCH","MATCH")

I think 1st solution is faster.

HTH
--
AP

"bxc2739" a écrit
dans le message de
...

Just to clarify (I'm not sure I explained it very good)

Imagine two sheets

Sheet #1 <-(Seibel converted to xls) Sheet #2 (xls sheet to be
worked on)
12345 54986 (mark as NO
MATCH)
84596 25746 (mark as NO
MATCH)
24578 12345 (mark as
MATCH!)
96328 15789 (mark as NO
MATCH)
45897 96328 (mark as
MATCH!)

The problem is Sheet #2 is not only five rows, it is like 30000+ rows,
and I
will have to do another 30000+ rows soon, so 60000+ to do by hand is
very slow indeed.

Also, 95% of all the rows in Sheet #2 will be a NO MATCH with no
matching zip codes to ANY of the zip codes of Sheet #1. So less than 5%
will actually be a match, but the thing is I have to check ALL of them
to actually know which ones match and which don't.

IS there a utility / macro or function in Excel that can quickly let me
do this the automated way?

Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile:
http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=534995

here is the example worksheet

4674

In example.xls:

Under the 'WORK' sheet/tab the first item is 'JOE DEL COLLETTI' with
ZIP code as 95003. I need to cross reference this zip code with the
'Siebel'
sheet (check to see if ANY of the rows in Siebel sheet have ZIP CODE as
95003) If ANY match at all, I go back and MARK (highlight in red) the
'JOE DEL COLLETTI' row (in WORK) to signal that the MATCH has been
found, if NOT found I leave it alone.

The majority 95% of the rows in WORK will have NO MATCH WHATSOEVER,
but I need to check all the 35000+ rows to find the <5% matches.

I hope this makes it clearer.

Thanks


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4674 |
+-------------------------------------------------------------------+

--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=534995