Need help pulling information from two sheets into a third sheet

ExcelinHR

New Member
Joined
Aug 2, 2014
Messages
20
Hello everyone,

I thought i could do this, but its Monday, and its 6pm, and my brain is fried. :/

This is the scenario:

I have information in one sheet which shows locations where my company has stores.

I have another sheet with thousands of rows of information, but i only need the rows which have the same identifiers as the first sheet.

Sheet 1: (made up numbers/cities)

LocationLocation CityLocation State
841PhoenixAZ
842TucsonAZ
641San DiegoCA
809SacramentoCA
814FresnoCA
822BakersfieldCA

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Sheet 2: (has many columns, but i shortened it to 2)

LoctnHrly Rate
82225.090000
82614.000000
82620.752500
82621.122500

<colgroup><col><col></colgroup><tbody>
</tbody>

Etc. etc.


From this example, we can see that 822 on sheet 2 matches one of the codes on sheet 1 "Bakersfield".

I need sheet 3 to only contain rows from sheet 2 with location codes that match sheet 1.

Any help with this would be amazing. I really don't want to do it by hand :|

Thanks
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
On Sheet 3 in Cell A2 (assuming A1 is your "Location" header):
=IFERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)),"")
This is an array formula and must be confirmed with CONTROL+SHIFT+ENTER or it won't work.
Copy down until you get blanks.
This formula will make a list of unique values from your data in Sheet 2. Adjust the ranges in the formula to reference your entire list of locations on sheet 2.

Dan
 
Upvote 0
Hey Dan,

Thanks for your response!
Unfortunately i think i may have miscommunicated what i need for a final result.

What i need on the last sheet is a list of all the items on sheet 2 that occur in sheet 1.
Basically i need sheet 1 to act as a filter for sheet 2 to output on sheet 3.

I would just filter the results, but there are hundreds of these numbers i would have to go through
 
Upvote 0
Ok. Change it to this:
=IFERROR(IF(COUNTIF(Sheet1!$A$2:$A$100,INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)))>0,INDEX(Sheet2!$A$2:$A$5,MATCH(0,COUNTIF($A$1:A1,Sheet2!$A$2:$A$5),0)),""),"")
This is an array formula and must be confirmed with CONTROL+SHIFT+ENTER or it won't work.

You'll need to adjust:COUNTIF(Sheet1!$A$2:$A$100

to match your data

Dan
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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