Excel/find matching values across workbooks
From a column of seventeen 10 digit varying numbers (column A), in 32 different workbooks, i would like to find the matching numbers that occur in ALL workbooks.
Is this possible & How do i do this please ?
Many thanks in advance
I see that I have misunderstood the question. Here is a revised answer:
Let us assume that all the workbooks are closed. In a master workbook you will enter formulas.
Now let us assume that each of the 32 workbooks has a single sheet named Sheet1 and the numbers are found in the range A1:A17 on Sheet1
further assume the 32 workbooks are found in the folder C:\Myfolders\Myfiles
so in your master workbook say in cell A1 you need to bring back all 17 of the numbers from the first workbook. Assume that the first workbook is MyWorkbook1.xlsx you would type in A1
then drag fill that down to A17
this will bring back all 17 numbers from MyWorkbook1.xlsx
So the number you seek has to be in this list of number. You will now enter formulas in B1:AF17
Now drag fill that down to row B17. That will check MyWorkbook2.xlsx for matches.
The formula will return #N/A if that location does not hold the value sought. If it does, it will tell you the number of the cell in the lookup range where the number is found.
Now continue in C1
Now drag fill that down to row C17. That will check MyWorkbook3.xlsx for matches.
Continue for the remain workbooks (out to column AF).
Now one row should have all numbers in it if there is a number found in every workbook. If there could be multiple numbers found in all workbooks, then any row that is all numbers will reflect numbers that appear in all workbooks.
Hopefully from that general example, you can craft a solution that fits your specific needs.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here