Cross Referencing multiple worksheets?

[ahatley]

Hi! So, I've gotten a little beyond my skill with excel for the most recent data that I have been asked to obtain from a set of spreadsheets that I have for an internship I'm doing.

Normally I would import them to Access but no one here knows how to use it nor do they have it installed on their systems so, I'm forced to figure out a way to use Excel to make it work. I'm hoping someone can help.

I have two major spreadsheets one with Names of companies with addresses on it that carry our product. The other spreadsheet has those same companies in it BUT with about 3,000 other companies and on multiple worksheets because they are all from different distributors and that is what it is organized by. I also need to know the individual products they are selling from this second spreadsheet and I would like to be able to weed out just these 400 some companies to one place without manually going in to each worksheet and using the find function and then copy/paste into a brand new workbook.

Any help?
Thanks.
AH

[Palmetto]

Not sure that I correctly understand your quest here, but it seems you want to extract those companies that sell your product using a master list of names.

There may be a better approach, but with not knowing the structure of your workbook, perhaps this will, at least, get you started.

WB1 = master, WB2 = "other spreadsheet"

From WB1, create a unique list of company names (if not already done)
Paste this list into a column in WB2

In WB2

• In an adjacent column, use the a COUNTIF function to count if the company name appears on the list you just pasted in. COUNTIF will return either 1 or 0.
  
• Use Advanced Filter with criteria, using the helper column (countif) as the criteria column.
• Set the criteria to ">0" and use the option to copy to a new location.
• Make sure to select the entire range of cells, including the helper column, when choosing the "List Range"

The resulting list should be only the suppliers / product whose name appears on your master list.

HTH,

Palmetto

[ahatley]

This is sort of what I need to do.
I have a partner company that asked for what retailers that carry their product carry our product and the specific product of ours they carry.

They supplied the retailers that carry their product...worksheet #1
(easily cross referenced with all my data using the MATCH function to figure out which retailers carry BOTH companies product) I was just looking for a yes or no answer at this point.

What I'm having issues with is when I need to pull the data out from worksheet #2 which actually contains our data of specific product for each retailer.
It is broken up into about 30 worksheets...and I don't know which retailer is in which worksheet.

So I'm wondering if there is a way to pull out each company without having to search for it in each worksheet, and then copy/ paste into a new worksheet? Or am I just looking forward to a whole lot of work because they aren't using Access here?