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

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


"joe_d_builder@yahoo.com" 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
>
>

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

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

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("'"&COLUMN(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
--

Thanks so much. Let me give this a try.

Joe

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" <demechanik@yahoo.com> wrote in message
news:%23qtoieeEGHA.1424@TK2MSFTNGP12.phx.gbl...
> 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("'"&COLUMN(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
> --
>
>