If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
searching a large database with a long list of search terms
I have a worksheet with a column with about 3000 rows of info in it. I
also have six other worksheets completely full (65536 each) that I need to search through a column and then when I find a row that matches an entry in one of those rows paste that row next to the correct number in the 3000 entries. I don't know how to write macros, only simple formulas. Is there an easy way to do this? This is confusing so as an example here's the one 3000 row worksheet aaa bob 123 bbb june 345 ccc fred 876 ddd mary 765 and I want to find all the values in the first colum (aaa, bbb, ccc, ddd) that show up in here (each of the 65536 row worksheets)... ddd toronto zzz chicago aaa new york mmm boise bbb portland ddd miami and end up with something that looks like this: aaa bob 123 new york bbb june 345 portland ccc fred 876 NO ENTRY ddd mary 765 miami Except that I need to do this with !hundreds of thousands! of rows so it can't take a super long time. Notice that when it couldn't find a matching entry it put "NO ENTRY" in there. That's important because there might be instances where the search term doesn't show up. Thanks so much folks, I really appreciate it. Joe |
#2
|
|||
|
|||
searching a large database with a long list of search terms
Hi,
Just a little clarification for myself and others that may look at this. The sheet with 3000 entries, these are unique entries? And this same sheet is where you want to write to? Just to add a term for clarification, this would be the "main" sheet. You would want to look up all entries from this "main" sheet and find them on the other 6 sheets, where you would be fetching back to the "main" sheet the city? The other 6 sheets, where the lookup is taking place, there are not duplicate lookup values, ie aaa, bbb, ccc would only have a single entry somewhere on the six other sheets and only one city associated with each lookup value? -- David " wrote: I have a worksheet with a column with about 3000 rows of info in it. I also have six other worksheets completely full (65536 each) that I need to search through a column and then when I find a row that matches an entry in one of those rows paste that row next to the correct number in the 3000 entries. I don't know how to write macros, only simple formulas. Is there an easy way to do this? This is confusing so as an example here's the one 3000 row worksheet aaa bob 123 bbb june 345 ccc fred 876 ddd mary 765 and I want to find all the values in the first colum (aaa, bbb, ccc, ddd) that show up in here (each of the 65536 row worksheets)... ddd toronto zzz chicago aaa new york mmm boise bbb portland ddd miami and end up with something that looks like this: aaa bob 123 new york bbb june 345 portland ccc fred 876 NO ENTRY ddd mary 765 miami Except that I need to do this with !hundreds of thousands! of rows so it can't take a super long time. Notice that when it couldn't find a matching entry it put "NO ENTRY" in there. That's important because there might be instances where the search term doesn't show up. Thanks so much folks, I really appreciate it. Joe |
#3
|
|||
|
|||
searching a large database with a long list of search terms
Yes, these are unique. They appear (most of them anyway) in the other
sheet just as you described. I messed around with using the advanced filter function and was able to filter the results but I wasn't able to copy over the data back to the "main" sheet. Plus I had to do each individual worksheet by itself and then copy all the results back to the main sheet (and then they weren't tied in with the original unique data). Joe |
#4
|
|||
|
|||
searching a large database with a long list of search terms
Joe,
In your example you have the code "ddd" twice - I'm assuming this is a typo, and have changed it to "eee" below. Is the data on the 6 sheets sorted in some way, and if not can it be sorted by the first column? You example data would then look like this: aaa new york bbb portland ddd toronto eee miami mmm boise zzz chicago This will make searching through the data to find a match much quicker. Pete |
#5
|
|||
|
|||
searching a large database with a long list of search terms
Perhaps one play to try ..
Sample construct available at: http://cjoint.com/?bfl6QazB5P VLookUp_6Sheets_joe_d_builder.xls Assume data in the 6 sheets are in cols A and B, from row1 down (key col = col A, "city" in col B) Rename the 6 sheets to be simply the numbers: 1,2,3,4,5,6 (The renaming of the sheetnames to the numbers 1 - 6 is to allow us to easily fill the extract formulas in Master) Then in sheet: Master where the data is in cols A to C, with the key col = col A aaa bob 123 bbb june 345 etc Put in D1, copy across 6 cols to I1, fill down as far as required: =IF($A1="","",IF(ISNA(MATCH($A1,INDIRECT("'"&COLUM N(A1)&"'!A:A"),0)),"NO ENTRY",VLOOKUP($A1,INDIRECT("'"&COLUMN(A1)&"'!A:B" ),2,0))) Cols D to I will extract the "city" returns from each of the 6 sheets (1 - 6) [ Col D = returns from sheet: 1, .. col I = returns from sheet: 6 ] Unmatched cases will return "NO ENTRY" -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
|
|||
|
|||
searching a large database with a long list of search terms
Thanks so much. Let me give this a try.
Joe |
#7
|
|||
|
|||
searching a large database with a long list of search terms
Max, I'm wondering if you did a full application test on this. (I didn't
look at your sample file) 6 sheets with 65536 rows of data, 3000 rows of lookup values and 6 columns of formulas. What kind of calc time did that take? Biff "Max" wrote in message ... Perhaps one play to try .. Sample construct available at: http://cjoint.com/?bfl6QazB5P VLookUp_6Sheets_joe_d_builder.xls Assume data in the 6 sheets are in cols A and B, from row1 down (key col = col A, "city" in col B) Rename the 6 sheets to be simply the numbers: 1,2,3,4,5,6 (The renaming of the sheetnames to the numbers 1 - 6 is to allow us to easily fill the extract formulas in Master) Then in sheet: Master where the data is in cols A to C, with the key col = col A aaa bob 123 bbb june 345 etc Put in D1, copy across 6 cols to I1, fill down as far as required: =IF($A1="","",IF(ISNA(MATCH($A1,INDIRECT("'"&COLUM N(A1)&"'!A:A"),0)),"NO ENTRY",VLOOKUP($A1,INDIRECT("'"&COLUMN(A1)&"'!A:B" ),2,0))) Cols D to I will extract the "city" returns from each of the 6 sheets (1 - 6) [ Col D = returns from sheet: 1, .. col I = returns from sheet: 6 ] Unmatched cases will return "NO ENTRY" -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
|
|||
|
|||
searching a large database with a long list of search terms
I would suggest you use ACCESS - Paste all of your data with the Names into 1 table in ACCESS. Paste your rows with cities in another table - generate a query which can pull the information together. The connection between the 2 would be the "AAA." -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498193 |
#9
|
|||
|
|||
searching a large database with a long list of search terms
I went skiing today so was only able to get to this tonight. Thanks
guys, this is good info. Most of what you're talking about is way beyond me though You know what I should do since this is a little much for me it seems... I should just write up exactly what I'm after and then let folks give me a price to do it (probably with a macro). Is that allowed on this group? I really don't have access to "excel" programmers locally and if I did they would propabably charge a great deal of money per hour where some sharp on this list could do it for extra cash. I'm guessing that it probably wouldn't take more than a couple of hours, maybe even an hour, for someone familiar with macros. Good tip about Access but I don't have that program and if I did would be even more lost than I am with Excel Joe |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format cells with a formula (7 conditions). | danindenver | General Discussion | 3 | January 2nd, 2006 02:40 PM |
Tasks, Assignments and Projects Database Structure. | Bernard Piette | Database Design | 2 | December 21st, 2005 01:30 PM |
HELP! CANNOT CONNECT TO SQL SERVER | Glint | General Discussion | 19 | May 9th, 2005 02:47 PM |
SUGGESTION: Shape search enhancements | tlonski | Visio | 1 | November 27th, 2004 09:39 PM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |