Hi Everyone,

I have a Sheet ( Named "Data" ) that has One Combination ( in the
Format 01-02-03-04-05-06 ) in Each Cell from A1:Z65000.
In Another Sheet ( Named "Draws" ) I have a Column ( Column "B" for
example ) with Several Hundred Draw Numbers in it.
What I would like to do is to Check if the Combination in Cell "B1" (
in the Sheet Named "Draws" ) is Anywhere in the "Data" Sheet. If it
is, it could put "Found", and if NOT put "Not Found" for example. Then
do the same for "B2","B3","B4" etc.
Is there a Formula that would achieve this or would it have to be done
using VB.

Thanks Very Much in Advance.
Paul

Hi
try the following formula 8note: this will probably slow Excel down due
to your large range - you may turn off automatic calculation):
=IF(COUNTIF('data'!$A$1:$Z$65000,B1)0,"Found","No t found")
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Paul Black wrote:
Hi Everyone,

I have a Sheet ( Named "Data" ) that has One Combination ( in the
Format 01-02-03-04-05-06 ) in Each Cell from A1:Z65000.
In Another Sheet ( Named "Draws" ) I have a Column ( Column "B" for
example ) with Several Hundred Draw Numbers in it.
What I would like to do is to Check if the Combination in Cell "B1" (
in the Sheet Named "Draws" ) is Anywhere in the "Data" Sheet. If it
is, it could put "Found", and if NOT put "Not Found" for example.
Then
do the same for "B2","B3","B4" etc.
Is there a Formula that would achieve this or would it have to be
done
using VB.

Thanks Very Much in Advance.
Paul

Hi Frank,

Thanks very much for the Formula, it works Great.
You are quite right though, it does Slow down Excel, even to the point
that I actually thought it had Stopped. Would it be Better to have a
Macro to get the Results and maybe list them in Column "D" for
example.

Thanks
Paul



"Frank Kabel" wrote in message ...
Hi
try the following formula 8note: this will probably slow Excel down due
to your large range - you may turn off automatic calculation):
=IF(COUNTIF('data'!$A$1:$Z$65000,B1)0,"Found","No t found")
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Paul Black wrote:
Hi Everyone,

I have a Sheet ( Named "Data" ) that has One Combination ( in the
Format 01-02-03-04-05-06 ) in Each Cell from A1:Z65000.
In Another Sheet ( Named "Draws" ) I have a Column ( Column "B" for
example ) with Several Hundred Draw Numbers in it.
What I would like to do is to Check if the Combination in Cell "B1" (
in the Sheet Named "Draws" ) is Anywhere in the "Data" Sheet. If it
is, it could put "Found", and if NOT put "Not Found" for example.
Then
do the same for "B2","B3","B4" etc.
Is there a Formula that would achieve this or would it have to be
done
using VB.

Thanks Very Much in Advance.
Paul

Hi Paul
you may try the following:
- first disable automatic calculation
- insert the formulas (copy them)
- re-calculate (hit F9)
- copy this column and goto 'Edit - Paste Special' and choose 'Values'
(to remove the formulas)
- enable automatic calculation.

This time you only have to wait once for the re-calculation


--
Regards
Frank Kabel
Frankfurt, Germany


Paul Black wrote:
Hi Frank,

Thanks very much for the Formula, it works Great.
You are quite right though, it does Slow down Excel, even to the
point
that I actually thought it had Stopped. Would it be Better to have a
Macro to get the Results and maybe list them in Column "D" for
example.

Thanks
Paul



"Frank Kabel" wrote in message
...
Hi
try the following formula 8note: this will probably slow Excel down
due to your large range - you may turn off automatic calculation):
=IF(COUNTIF('data'!$A$1:$Z$65000,B1)0,"Found","No t found")
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Paul Black wrote:
Hi Everyone,

I have a Sheet ( Named "Data" ) that has One Combination ( in the
Format 01-02-03-04-05-06 ) in Each Cell from A1:Z65000.
In Another Sheet ( Named "Draws" ) I have a Column ( Column "B" for
example ) with Several Hundred Draw Numbers in it.
What I would like to do is to Check if the Combination in Cell "B1"
( in the Sheet Named "Draws" ) is Anywhere in the "Data" Sheet. If
it is, it could put "Found", and if NOT put "Not Found" for
example.
Then
do the same for "B2","B3","B4" etc.
Is there a Formula that would achieve this or would it have to be
done
using VB.

Thanks Very Much in Advance.
Paul