Excel - Find and match data from one sheet in another?

Question

Hello!

I think that might be a long shot but I know excel capabilities are huge, so I'm hoping that someone can help me find a way to resolve my issue.

I have 2 excel documents. One (doc A) has a list of locations (stores). That list has about 5k rows. I have another list (doc B) with selected locations but I ONLY have the store numbers (that will match the one of the data columns in document A). There is about 375 rows in doc B. Is there a way to edit/sort/select the document A so that the only rows left are the stores matching the list from the document B?

I was considering using conditional formatting or "find and select" but I have never used those options with so many data points. I'm attaching snippet of each sheet to help visualize the issue.

What resolution would you suggest?

Thank you

A

B

Answer 1

You can use Advanced Filter, but before you do that, make A1 on both sheets identical.

Click anywhere in doc A.

On the Data tab of the ribbon, in the Sort & filter group, click Advanced.

Excel should select the entire range and show its address in the 'List range' box.

Click in the 'Criteria range' box.

Switch to the other tab and select the range of store numbers (including the header in A1).

Click OK.

Answer 2

Worked like a charm, just learned something new. Thank you!