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
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 -- |
#7
|
|||
|
|||
searching a large database with a long list of search terms
Thanks so much. Let me give this a try.
Joe |
#8
|
|||
|
|||
searching a large database with a long list of search terms
You're welcome, Joe.
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- wrote in message oups.com... Thanks so much. Let me give this a try. Joe |
#9
|
|||
|
|||
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 -- |
#10
|
|||
|
|||
searching a large database with a long list of search terms
"Biff" wrote:
Max, I'm wondering if you did a full application test on this .. Obviously not g . Btw, do you do full testing on detailed posts like this one before you respond ? 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? Ok, I just did that, on my laptop (3 year old IBM T30) Excel 97: Took about 3 mins to fill the formulas & complete calc. Perhaps you would like to indicate what other pre-emptive caveats should have been written in my suggestion ? Like always, it was only a suggestion for the OP to try out. And nothing is ever stated in my posts that precludes any others from posting other, possibly better suggestions -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
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 |