Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help find automate way to cross reference two GAINT excel databases!!
Need help find automate way to cross reference two GAINT excel databases!! 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/ 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 -Bo -- bxc2739 ------------------------------------------------------------------------ bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538 View this thread: http://www.excelforum.com/showthread...hreadid=535001 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help find automate way to cross reference two GAINT excel databases!!
On Fri, 21 Apr 2006 10:30:14 -0500, bxc2739
wrote: Need help find automate way to cross reference two GAINT excel databases!! 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/ 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 -Bo Have you tried a standard VLOOKUP? Assuming the Zip Codes on sheet 1 are in say A1:A1000 and in A1:A30000 on sheet 2 on sheet 2 in a spare column enter =IF(ISERROR(VLOOKUP(A1,Sheet1!A1:A1000,1,false))," No Match","Match) and copy down. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace should work in Excel text boxes | Excel Discussion (Misc queries) | |||
how to automate invoice using excel database | Excel Discussion (Misc queries) | |||
cross reference two spreadsheets using MS Excel | Excel Worksheet Functions | |||
Find differences between two Excel workbooks or worksheets | Excel Discussion (Misc queries) | |||
Linking Excel files -- I cannot find an answer anywhere. | Excel Discussion (Misc queries) |