Thanks! I found out what the problem is....it's not in the formula that you
are giving me or the lack of me following directions. I just discovered that
the Part#'s listed from the first workbook have spaces after them that aren't
supposed to be there. Do you know how I can delete the spaces with a formula?
I just went through a few of the part's and it looks like each part# has 9
spaces after the part# ends.

"Gary L Brown" wrote:

Assumptions:
- BigList.xls is the name of the "spreadsheet that contains
hundreds and hundreds of part#'s."
- LotsOfTabs.xls is the name of the "different spreadsheet
that has 30 different tabs of data."
- Both files are open.
- When you are talking about a spreadsheet, you really
mean workbook.
- When you are talking about a tab, you really mean a worksheet
within a workbook.
- The workbook BigList.xls contains 1 worksheet called 'MyList'
- The worksheet MyList has 3 columns of data.
- The column of part#s is in column C and has a heading in
cell C1 and says 'Parts'
- The part#s start in cell C2 and go down to cell C10
- The workbook LotsOfTabs.xls contains 30 worksheets named 'Sheet1',
'Sheet2', 'Sheet3', ... , 'Sheet30'.
- The worksheets in LotsOfTabs.xls all have 8 columns of data.
- The column of part#s is Column H for all worksheets
in LotsOfTabs.xls
- The part#s in both workbooks are similarly formatted.
- Ex: Part 'ABC 123' is NOT the same as Part 'ABC123' = 0
Part 'ABC 123 ' is NOT the same as Part 'ABC 123' = 0
Part 'ABC 123' IS the same as Part 'abc 123' = 1

Actions:
- In workbook BigList.xls, worksheet MyList, in cell D1, put a
heading called 'Found = 1'
- In cell D2 put the formula...
=IF(ISNA(VLOOKUP($C2,[LotsOfTabs.xls]Sheet1!$H:$H,1,False)),0,1)
- Copy this formula from E2 to AG2.
- Edit each formula changing Sheet1 to the appropriate
Sheet name
- In this example, change E2 to Sheet2, F2 to Sheet3,
G2 to Sheet4, ... , AG2 to Sheet30
- In cell AH2 put the formula...
=SUM(D2:AG2)
- Copy D2:AH2 down to D10:AH10
- Look for -0-s in column AH for part#s that are not in LotsOfTabs.xls

Results:
- If, at this point, you do not see any '1's, there are only 2 possibilities.
1) The part#s are not formatted the same
2) The part in BigList.xls is not in LotsOfTabs.xls

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"chelldog" wrote:

I must be doing something wrong because the only outcome I get is "0"....no
"1's". Which sheet/column as I actually putting the formula in to? I tried
starting with either spreadsheet but that didn't change the outcome. Thanks
for your patience!

"Gary L Brown" wrote:

Example of the formula to check the first of 30 tabs of data.
=IF(ISNA(VLOOKUP($C2,[Book2.xls]Sheet1!$H:$H,1,FALSE)),0,1)
[Book2.xls] should be changed to the 'different spreadsheet'
Sheet1 should be changed to the name of the first of the 30 tabs of data

=IF(ISNA(VLOOKUP($C2,[Book2.xls]Sheet2!$H:$H,1,FALSE)),0,1)
Sheet2 should be changed to the name of the second of the 30 tabs of data

etc., etc., etc.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"chelldog" wrote:

Thank you....so, what would the formula look like if the part#'s from my
first file were in column C and the part# field on the second file was in
column H on each tab?

"Gary L Brown" wrote:

30 vlookups that you then copy down 650 rows?
=if(isna(VLOOKUP(A2,'[MyFile.xls]MySheet1'!$A:$D,4,FALSE)),0,1)
=if(isna(VLOOKUP(A2,'[MyFile.xls]MySheet2'!$A:$D,4,FALSE)),0,1)
...
=if(isna(VLOOKUP(A2,'[MyFile.xls]MySheet30'!$A:$D,4,FALSE)),0,1)
etc.
Using something like the example formula above where if it finds the item it
will put a -1- in the cell, if it doesn't, it will put a -0- in the cell, in
the 31st column, put a =sum(C:AH) [or whatever the columns are]. Check that
column. If you see something other than -0-, you've got a match.
HTH,
--
Gary Brown

"chelldog" wrote:

I have a spreadsheet that contains hundreds and hundreds of part#'s. My
project is to determine whether or not these part#'s are in a different
spreadsheet that has 30 different tabs of data. Is there is an easier way of
doing this other then manual queries?