Find and return data based on a range

[egeorge4]

Hi folks!

Looking for the best way to perform a painfully repetitive task!

I have 2 sheets of data.

- A master sheet with all records (customer data - about 65,000
records/row)
- A completion sheet with partial data (customer data - about 50
records/rows)


The master sheet looks like this:

Job Num Job Type Tech Name City
State
1069478 Commercial CH25 Joe Smith Ashlan KY
1068202 Commercial CH25 Steve Jones Portsmouth OH
1061548 Commercial CH25 Kevin Phinney Portsmouth OH
1014500 Residential CH25 Arnold Wright Argillite KY
1071035 Residential CH25 Tim Blue huntington WV

The completion sheet looks like this:

Job Num Status
1068202 Completed
1061548 Completed
1071035 Completed


I need to use the "Job Number" range from the completion sheet to pull
the entire matching record/row from the master sheet and paste or
display it in a new sheet or the current sheet or .... heck I will take
it anywhere! As long as I can search for all completed jobs from the
completion sheet in the master sheet all at once!

Any help on this is much appreciated! We have been trying to figure
this one out for a long time!

 

[Ardus Petus]

In Completion sheet cell C2, enter:
=VLOOKUP($A2,master!$A:$E,COLUMN(C2)-1,0)
copy to the right, then copy down

HTH

 

[egeorge4]

Get an error message:

Excel cannot complete this task with available resources. Choose less
data or close other applications.

All other applications are closed and I am pretty sure this PC should
be able to handle this task...

Any other suggestions?

 

[egeorge4]

Is there any way to use the Query Wizard to search for the multiple job
numbers?

 

[egeorge4]

The master sheet has 51,000 records/rows.

Is that to big for VLookup to handle?

 

[Ardus Petus]

Works by me (XP Pro + Office 2000 / P4 2Mhz 256 k)

HTH

 

[egeorge4]

Thanks for the help and all the views!

I think I have an answer:
http://www.mrexcel.com/board2/viewtopic.php?t=217752

Thanks again!

 

[MaC]

I prefer ADO programming in such cases. You can use SQL query based on data
from few sheets and put result into another sheet.
Even more - if your master sheet will be more than 65536 data rows you can
use access MDB file to store your data.

MaC

U¿ytkownik "egeorge4"