Comparing Data
 

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

The best way to learn is to let XL guide you as to how to specify the various
arguments of any function.

Select the cell where you want the function.

Click the Fx button (it's on the formula bar).

In the dialog box, in the 'select a category' dropdown select 'Lookup and
Reference' (or just select 'All')

In the 'Select a function' box, select the MATCH function (type the first
few letters and XL will jump to functions starting with M, then MA, then MAT,
etc).

Click OK and you'll get the function arguments dialog box.

Click in the lookup_value box, then use the mouse to click the cell whose
value you want to look up.

Next, click in the lookup_range box and use the mouse to select the range
you want to search. If necessary, use the worksheet tab to switch to another
worksheet before selecting the range of interest.

Specify a value for the last argument. Enter zero if you want an exact
match with unsorted data.

Click OK to exit the dialog box (or just press ENTER).

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

Ok I did a little testing by making up 10 fake invoice numbers (8 digits
long) that are in Sheet1. In Sheet2 I put in the same 10 fake invoice
numbers plus inserted a few random fake numbers just to test to see what
happened. With the formula below it didn't give me a true/false answer as I
had hoped, but I did notice that if it couldn't find a number it gave #N/A as
an answer. With the ones it was able to find it either gave me a 1 or 3
which is odd. Can you explain to me why it did it this way? Maybe point me
to a website that will help explain my question to you?

=MATCH(RC[-2],Sheet2!R[1]C[-2]:R[3]C[-2],1)

Thank you again for your time. I appreciate it.

Matt



"Tushar Mehta" wrote:

The best way to learn is to let XL guide you as to how to specify the various
arguments of any function.

Select the cell where you want the function.

Click the Fx button (it's on the formula bar).

In the dialog box, in the 'select a category' dropdown select 'Lookup and
Reference' (or just select 'All')

In the 'Select a function' box, select the MATCH function (type the first
few letters and XL will jump to functions starting with M, then MA, then MAT,
etc).

Click OK and you'll get the function arguments dialog box.

Click in the lookup_value box, then use the mouse to click the cell whose
value you want to look up.

Next, click in the lookup_range box and use the mouse to select the range
you want to search. If necessary, use the worksheet tab to switch to another
worksheet before selecting the range of interest.

Specify a value for the last argument. Enter zero if you want an exact
match with unsorted data.

Click OK to exit the dialog box (or just press ENTER).

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

OK, so now you know that MATCH returns #N/A if it doesn't find a match and a
number (which happens to be the index into the range) if it does. So, put
the result of MATCH in a ISNA() function or a ISNUMBER() function depending
on when you want a TRUE.

ISNA(MATCH(...)) will yield a TRUE if there is no match and a FALSE if there
is.

ISNUMBER(MATCH(...)) will yield a TRUE if there is a match and a FALSE if
there is not.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Ok I did a little testing by making up 10 fake invoice numbers (8 digits
long) that are in Sheet1. In Sheet2 I put in the same 10 fake invoice
numbers plus inserted a few random fake numbers just to test to see what
happened. With the formula below it didn't give me a true/false answer as I
had hoped, but I did notice that if it couldn't find a number it gave #N/A as
an answer. With the ones it was able to find it either gave me a 1 or 3
which is odd. Can you explain to me why it did it this way? Maybe point me
to a website that will help explain my question to you?

=MATCH(RC[-2],Sheet2!R[1]C[-2]:R[3]C[-2],1)

Thank you again for your time. I appreciate it.

Matt



"Tushar Mehta" wrote:

The best way to learn is to let XL guide you as to how to specify the various
arguments of any function.

Select the cell where you want the function.

Click the Fx button (it's on the formula bar).

In the dialog box, in the 'select a category' dropdown select 'Lookup and
Reference' (or just select 'All')

In the 'Select a function' box, select the MATCH function (type the first
few letters and XL will jump to functions starting with M, then MA, then MAT,
etc).

Click OK and you'll get the function arguments dialog box.

Click in the lookup_value box, then use the mouse to click the cell whose
value you want to look up.

Next, click in the lookup_range box and use the mouse to select the range
you want to search. If necessary, use the worksheet tab to switch to another
worksheet before selecting the range of interest.

Specify a value for the last argument. Enter zero if you want an exact
match with unsorted data.

Click OK to exit the dialog box (or just press ENTER).

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

I got it working the way I wanted it to with ISNUMBER() thank you so much for
your help.

"Tushar Mehta" wrote:

OK, so now you know that MATCH returns #N/A if it doesn't find a match and a
number (which happens to be the index into the range) if it does. So, put
the result of MATCH in a ISNA() function or a ISNUMBER() function depending
on when you want a TRUE.

ISNA(MATCH(...)) will yield a TRUE if there is no match and a FALSE if there
is.

ISNUMBER(MATCH(...)) will yield a TRUE if there is a match and a FALSE if
there is not.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Ok I did a little testing by making up 10 fake invoice numbers (8 digits
long) that are in Sheet1. In Sheet2 I put in the same 10 fake invoice
numbers plus inserted a few random fake numbers just to test to see what
happened. With the formula below it didn't give me a true/false answer as I
had hoped, but I did notice that if it couldn't find a number it gave #N/A as
an answer. With the ones it was able to find it either gave me a 1 or 3
which is odd. Can you explain to me why it did it this way? Maybe point me
to a website that will help explain my question to you?

=MATCH(RC[-2],Sheet2!R[1]C[-2]:R[3]C[-2],1)

Thank you again for your time. I appreciate it.

Matt



"Tushar Mehta" wrote:

The best way to learn is to let XL guide you as to how to specify the various
arguments of any function.

Select the cell where you want the function.

Click the Fx button (it's on the formula bar).

In the dialog box, in the 'select a category' dropdown select 'Lookup and
Reference' (or just select 'All')

In the 'Select a function' box, select the MATCH function (type the first
few letters and XL will jump to functions starting with M, then MA, then MAT,
etc).

Click OK and you'll get the function arguments dialog box.

Click in the lookup_value box, then use the mouse to click the cell whose
value you want to look up.

Next, click in the lookup_range box and use the mouse to select the range
you want to search. If necessary, use the worksheet tab to switch to another
worksheet before selecting the range of interest.

Specify a value for the last argument. Enter zero if you want an exact
match with unsorted data.

Click OK to exit the dialog box (or just press ENTER).

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt

Comparing Data
 

You are welcome.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I got it working the way I wanted it to with ISNUMBER() thank you so much for
your help.

"Tushar Mehta" wrote:

OK, so now you know that MATCH returns #N/A if it doesn't find a match and a
number (which happens to be the index into the range) if it does. So, put
the result of MATCH in a ISNA() function or a ISNUMBER() function depending
on when you want a TRUE.

ISNA(MATCH(...)) will yield a TRUE if there is no match and a FALSE if there
is.

ISNUMBER(MATCH(...)) will yield a TRUE if there is a match and a FALSE if
there is not.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Ok I did a little testing by making up 10 fake invoice numbers (8 digits
long) that are in Sheet1. In Sheet2 I put in the same 10 fake invoice
numbers plus inserted a few random fake numbers just to test to see what
happened. With the formula below it didn't give me a true/false answer as I
had hoped, but I did notice that if it couldn't find a number it gave #N/A as
an answer. With the ones it was able to find it either gave me a 1 or 3
which is odd. Can you explain to me why it did it this way? Maybe point me
to a website that will help explain my question to you?

=MATCH(RC[-2],Sheet2!R[1]C[-2]:R[3]C[-2],1)

Thank you again for your time. I appreciate it.

Matt



"Tushar Mehta" wrote:

The best way to learn is to let XL guide you as to how to specify the various
arguments of any function.

Select the cell where you want the function.

Click the Fx button (it's on the formula bar).

In the dialog box, in the 'select a category' dropdown select 'Lookup and
Reference' (or just select 'All')

In the 'Select a function' box, select the MATCH function (type the first
few letters and XL will jump to functions starting with M, then MA, then MAT,
etc).

Click OK and you'll get the function arguments dialog box.

Click in the lookup_value box, then use the mouse to click the cell whose
value you want to look up.

Next, click in the lookup_range box and use the mouse to select the range
you want to search. If necessary, use the worksheet tab to switch to another
worksheet before selecting the range of interest.

Specify a value for the last argument. Enter zero if you want an exact
match with unsorted data.

Click OK to exit the dialog box (or just press ENTER).

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

Hi thanks for responding Tushar I did find it but it's not very specific as
to how to do it when you have more than 1 sheet inside a workbook. If you
can be a little more specific I'd greatly appreciate it :).

Thanks again,

Matt

"Tushar Mehta" wrote:

Check out XL help for the MATCH function.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Speedy Brewskie" wrote:

I would 1st like to say thank you for anyone who reads this as what I am
currently doing is taking way to long and I'm sure there is a better way of
speeding this up.

This is what I'm trying to do. I have a workbook that contains 2 worksheets
(lets say 1 of them is called sheet1 and the other is sheet2) and I'm looking
at sheet1 to see if the same exact invoice number is on sheet2. I would like
to know if there is a forumula that will automatically look for the same
invoice number in sheet2 and display a true/false answer in sheet1 to let me
know whether or not the same invoice number exists in both worksheets.

If it would be rather difficult you think for me to do it this way I'm open
to other idea's. Thank you again for your time.

- Matt