Find and return data based on a range Please Help---

E

egeorge4

New Member
Joined
Jun 18, 2006
Messages
7
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 Phin Portsmouth OH
1014500 Residential CH25 Bob 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 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 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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
I think you've already got reply in the other web site.....

Multi post is not a good idea.
 
Upvote 0
Matter of fact, the other suggestion I received is not working.

Maybe someone on this forum has a better answer.

See what I mean?
 
Upvote 0
Thanks for understanding and I'm sure someone will come to you with
brilliant solution.
 
Upvote 0
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 Phin Portsmouth OH
1014500 Residential CH25 Bob 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 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 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!
Click to expand...

Master
Book6
ABCDEF
1Job NumJob TypeTechNameCityState
21069478CommercialCH25Joe SmithAshlanKY
31068202CommercialCH25Steve JonesPortsmouthOH
41061548CommercialCH25Kevin PhinPortsmouthOH
51014500ResidentialCH25Bob WrightArgilliteKY
61071035ResidentialCH25Tim BluehuntingtonWV
7
8
Master


Completed
Book6
ABCDEFGH
1Job NumStatusIdxJob TypeTechNameCityState
21068202Completed2CommercialCH25Steve JonesPortsmouthOH
31061548Completed3CommercialCH25Kevin PhinPortsmouthOH
41071035Completed5ResidentialCH25Tim BluehuntingtonWV
5
Completion


C2, copied down:

=MATCH(A2,Master!$A$2:$A$6,0)

D2, copied across and down:

=IF(ISNUMBER($C2),INDEX(Master!B$2:B$6,$C2),"")

If you can sort the data on Master on column A (Job Num) in ascending order and keep data in sorted order, replace the formulas in C2 and in D2 on Completion with:

=IF(LOOKUP(A2,Master!$A$2:$A$6)=A2,MATCH(A2,Master!$A$2:$A$6,1),0)

=IF($C2,INDEX(Master!B$2:B$6,$C2),"")

for more speed.

If you are on Excel 2003, convert A1:H4 on Completion into a List by means of Data|List|Create List. Whenever you add a new Job Num that is completed, all retrieval related to it will occur automatically.
 
Upvote 0
I think that might be it!!!

I have to tinker around with it a bit, but I feel very close!

Thank You!

Couple Questions:

-What is the "index" Column for (C2, copied down)?

-Should it be returning a 0 (zero)?

-The cells that are blank on the master are returning a 0 (zero)...is that related to the previous questions about C2 or somethimg different?

-How do I make it return a blank cell if the cells that are blank on the master?



Thanks Again!
 
Upvote 0
...

Couple Questions:

-What is the "index" Column for (C2, copied down)?
Click to expand...

Read on the MATCH function in Excel's Help.

-Should it be returning a 0 (zero)?
Click to expand...

No.

-The cells that are blank on the master are returning a 0 (zero)...is that related to the previous questions about C2 or somethimg different?

-How do I make it return a blank cell if the cells that are blank on the master?
Click to expand...

Select D2:H4 and apply the following custom format:

[=0]"";General
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Please help with a split of 1 spreadsheet into many based on the unique data.
Replies
1
Views
571
b.downey
b.downey

Forum statistics

Threads
1,216,126
Messages
6,129,021
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top