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?

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?

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?

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?

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?

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?

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?

Use the Trim function
=Trim(A1)

- insert a column next to the Part#s column
- use the trim function
- Paste/value the column
- either copy the part #s or delete the 'old' column

HTH,
--
Gary Brown

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


"chelldog" wrote:

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?

Yeeeeeaaaaaahhhhh! That did the trick, thanks Gary!

"Gary L Brown" wrote:

Use the Trim function
=Trim(A1)

- insert a column next to the Part#s column
- use the trim function
- Paste/value the column
- either copy the part #s or delete the 'old' column

HTH,
--
Gary Brown

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


"chelldog" wrote:

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?

np
Glad I could help.
:O
--
Gary Brown

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


"chelldog" wrote:

Yeeeeeaaaaaahhhhh! That did the trick, thanks Gary!

"Gary L Brown" wrote:

Use the Trim function
=Trim(A1)

- insert a column next to the Part#s column
- use the trim function
- Paste/value the column
- either copy the part #s or delete the 'old' column

HTH,
--
Gary Brown

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


"chelldog" wrote:

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?