Excel Compare Strings in Multiple Sheets

**Nu2Java** · 08-24-2012, 05:00 PM

Hello,

I am trying to figure out a way to find common strings in multiple sheets and then give a list of the common strings found and how many times each one was found. What I need to have is .... Sheet1 has a list of part numbers in Column A for a given job. The list could have as many as 120 part numbers. This continues for each job in Sheet2, Sheet3 etc.

Let's say I start with 50 Sheets containing part numbers for a job. How can I find the common parts across all Sheets? And then give me a list of the common (or exact matches) found in more than 1 Sheet. Then tell me how many times it was found? Like it finds Part Number: "CA100-87" that was found on more than 1 Sheet and was found in 26 out of 50 Sheets.

I hope I am making sense for what I am asking for. Thank You for your time and help in advance!

**Max, Singapore** · 08-24-2012, 06:13 PM

Your intents are many and quite complex ..

This should give you a head start in producing a powerful Enquiries sheet ..
Insert a new sheet, label as say: Enquiries, move it to leftmost
In Enquiries,
List in B1 across all the part nums to be checked across all sheets, eg: CA100-87, etc
List in A2 down all the 50 sheetnames, eg: Sheet2, Sheet3, etc
Put this in B2:
=IF(OR(B$1="",$A2=""),"",IF(COUNTIF(INDIRECT("'"&$A2&"'!A:A"),B$1),"x",""))
Copy across / fill down to populate results. It'll flag as "x" where the part numbers are found. COUNTIF can be used to check for existence of items, and within it, INDIRECT can be used to read sheetnames, enabling great ease in formula propagation.

Note: To generate the 50 sheetnames list,
you can run the sub below in a new sheet
---------------------

Please Login or Register  to view this content.

------------------

Don't forget ... click the little star at the bottom left of this response to celebrate success

**Nu2Java** · 08-24-2012, 11:39 PM

Hi Max, Singapore

Thanks for your time and help... this is very close to what I need. I will try this out and see how it works.

**Max, Singapore** · 08-25-2012, 08:09 AM

Welcome, do take a moment to click the little star at the bottom left of my responses

Excel Compare Strings in Multiple Sheets

LinkBack

Thread Tools

Rate This Thread

Display

Excel Compare Strings in Multiple Sheets

Re: Excel Compare Strings in Multiple Sheets

Re: Excel Compare Strings in Multiple Sheets

Re: Excel Compare Strings in Multiple Sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions