A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

searching a large database with a long list of search terms



 
 
Thread Tools Display Modes
  #1  
Old January 5th, 2006, 06:50 AM posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 08:10 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 08:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 10:01 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 10:53 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 12:01 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 5th, 2006, 07:06 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default searching a large database with a long list of search terms

Thanks so much. Let me give this a try.

Joe

  #8  
Old January 5th, 2006, 11:24 PM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 6th, 2006, 03:31 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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  
Old January 6th, 2006, 04:55 AM posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Format cells with a formula (7 conditions). danindenver General Discussion 3 January 2nd, 2006 03:40 PM
Tasks, Assignments and Projects Database Structure. Bernard Piette Database Design 2 December 21st, 2005 02: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 10:39 PM
synchronizing form and list box Deb Smith Using Forms 8 June 21st, 2004 08:15 PM


All times are GMT +1. The time now is 05:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.