Hello,

I am not sure how to use excel to do the following (eventually i woul
like a macro to automatically perform the task).

I need to match a date from one table to another, and if the date
match i need to insert a number into a cell. If they do not match
also need a number to be entered into a cell but this number will b
different to if the dates do match.

Could anyone advise me on how to do this??

Thankyou in advanc

--
PaulOakle
-----------------------------------------------------------------------
PaulOakley's Profile: http://www.excelforum.com/member.php...fo&userid=2510
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

Hey, Paul. We'll need to know what that number is or where it's coming from,
or the methodology deciding what it is, before we can help you get it into
that cell. Offhand, however:

=if(b2=a2,"number","other number")
*******************
~Anne Troy

www.OfficeArticles.com


"PaulOakley" wrote
in message ...

Hello,

I am not sure how to use excel to do the following (eventually i would
like a macro to automatically perform the task).

I need to match a date from one table to another, and if the dates
match i need to insert a number into a cell. If they do not match i
also need a number to be entered into a cell but this number will be
different to if the dates do match.

Could anyone advise me on how to do this??

Thankyou in advance


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

Your query is not clear, but with certain assumptions:

Your first table is in A1:B10, and the second in D1:E10, then us
something like, in cell G1 (say), enter:
=IF(A1=D1,1,0)
and drag down to copy for all the 10 rows.
1 is entered for a match, and 0 for otherwise.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PaulOakley" wrote
in message ...

Hello,

I am not sure how to use excel to do the following (eventually i would
like a macro to automatically perform the task).

I need to match a date from one table to another, and if the dates
match i need to insert a number into a cell. If they do not match i
also need a number to be entered into a cell but this number will be
different to if the dates do match.

Could anyone advise me on how to do this??

Thankyou in advance


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

=IF(NOT(ISNA(MATCH(A2,M1:M100,0))),1,2)

where A2 is the date in one table, M1:m100 is the other table of dates.

Adjust to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"PaulOakley" wrote
in message ...

Hello,

I am not sure how to use excel to do the following (eventually i would
like a macro to automatically perform the task).

I need to match a date from one table to another, and if the dates
match i need to insert a number into a cell. If they do not match i
also need a number to be entered into a cell but this number will be
different to if the dates do match.

Could anyone advise me on how to do this??

Thankyou in advance


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

Hey,

Thanks for your relpies, they are really appreciated, my first messag
is a bit unclear.

I need one table which has w\commencing dates in it to check thes
dates with another table, if the w\c date is matched with a date in th
other table then i need it to return the value 12.5, if it is not the
the value 0 needs to be returned.

Some background which may help is that the system i am building i
taking into account the second sunday of every month, where 12.5 hour
of cleaning is required (this will be placed in a cell and the
deducted from the total hours of operations). I have built a seperat
table in another sheet with the w\c date of the second sunday of ever
month. My problem is that many of the week commencing dates that I a
checking are not present in the new sheet.

I hope this helps clarify my problem a bit more,

Thankyou in advance

--
PaulOakle
-----------------------------------------------------------------------
PaulOakley's Profile: http://www.excelforum.com/member.php...fo&userid=2510
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

Still applies, different values, but I thought you could have figured that

=IF(NOT(ISNA(MATCH(A2,M1:M100,0))),12.5,0)

where A2 is the date in one table, M1:m100 is the other table of dates.

Adjust to suit.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"PaulOakley" wrote
in message ...

Hey,

Thanks for your relpies, they are really appreciated, my first message
is a bit unclear.

I need one table which has w\commencing dates in it to check these
dates with another table, if the w\c date is matched with a date in the
other table then i need it to return the value 12.5, if it is not then
the value 0 needs to be returned.

Some background which may help is that the system i am building is
taking into account the second sunday of every month, where 12.5 hours
of cleaning is required (this will be placed in a cell and then
deducted from the total hours of operations). I have built a seperate
table in another sheet with the w\c date of the second sunday of every
month. My problem is that many of the week commencing dates that I am
checking are not present in the new sheet.

I hope this helps clarify my problem a bit more,

Thankyou in advance.


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

=if(isnumber(Match(A1,Sheet1!$A$1:$A$500,0)),12.5, 0)

--
Regards,
Tom Ogilvy

"PaulOakley" wrote
in message ...

Hey,

Thanks for your relpies, they are really appreciated, my first message
is a bit unclear.

I need one table which has w\commencing dates in it to check these
dates with another table, if the w\c date is matched with a date in the
other table then i need it to return the value 12.5, if it is not then
the value 0 needs to be returned.

Some background which may help is that the system i am building is
taking into account the second sunday of every month, where 12.5 hours
of cleaning is required (this will be placed in a cell and then
deducted from the total hours of operations). I have built a seperate
table in another sheet with the w\c date of the second sunday of every
month. My problem is that many of the week commencing dates that I am
checking are not present in the new sheet.

I hope this helps clarify my problem a bit more,

Thankyou in advance.


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

Thanks for your response, the formulae is having problems with the match
type? In the forumale you provided this is the '0' after the lookup
range, do you have any idea what the match type is?

I have tried changing the match type but this makes the result return
12.5 every time.

Thankyou


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

Sorry Bob, perhaps my last post was not correctly phrased,
I understand everything about the formulae apart from the match type,

I have attached a word document with some screen shots because i thin
i may be explaining the problem incorrectly.

The first doc appropriately named help! shows the formulae in the ba
and the cell i am trying to reference. The week commencing in yello
should have a concession value of 12.5.

The second doc shows where i want to match the data to.

Any help will be much appreciated. Cant get my head around this one
netiher can the IT dept at a world wide manufacture believe it or not!

Thank

+-------------------------------------------------------------------
|Filename: Printed#.doc
|Download: http://www.excelforum.com/attachment.php?postid=3596
+-------------------------------------------------------------------

--
PaulOakle
-----------------------------------------------------------------------
PaulOakley's Profile: http://www.excelforum.com/member.php...fo&userid=2510
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

That is an exact match, so if you get 12.5 every time, it either means it
finds it every time, or doesn't, depending upon where the 12.5 is.

It might due to copying and updating the formula, try


=IF(NOT(ISNA(MATCH(A2,$M$1:$M$100,0))),12.5,0)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"PaulOakley" wrote
in message ...

Thanks for your response, the formulae is having problems with the match
type? In the forumale you provided this is the '0' after the lookup
range, do you have any idea what the match type is?

I have tried changing the match type but this makes the result return
12.5 every time.

Thankyou


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

Thanks very much for your help Bob, it turns out that the reason the
were not linking was due to a slight difference in date format!
appear to have become blind to this due to my hours of starring at th
screen.

Many Thank

--
PaulOakle
-----------------------------------------------------------------------
PaulOakley's Profile: http://www.excelforum.com/member.php...fo&userid=2510
View this thread: http://www.excelforum.com/showthread.php?threadid=38672

One problem is you have an error in your formula. Instead of looking at a
single row, your second part of you lookup table refers to AE24471 rather
than AE2447 or whatever the row actually was. Get your formula right
first.

--
Regards,
Tom Ogilvy


"PaulOakley" wrote
in message ...

Sorry Bob, perhaps my last post was not correctly phrased,
I understand everything about the formulae apart from the match type,

I have attached a word document with some screen shots because i think
i may be explaining the problem incorrectly.

The first doc appropriately named help! shows the formulae in the bar
and the cell i am trying to reference. The week commencing in yellow
should have a concession value of 12.5.

The second doc shows where i want to match the data to.

Any help will be much appreciated. Cant get my head around this one,
netiher can the IT dept at a world wide manufacture believe it or not!

Thanks


+-------------------------------------------------------------------+
|Filename: Printed#.doc |
|Download: http://www.excelforum.com/attachment.php?postid=3596 |
+-------------------------------------------------------------------+

--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726

You must be mistaken. Match, used in a worksheet is not sensitive to date
format. More than likely, you corrected the error I cited.

--
Regards,
Tom Ogilvy

"PaulOakley" wrote
in message ...

Thanks very much for your help Bob, it turns out that the reason they
were not linking was due to a slight difference in date format! I
appear to have become blind to this due to my hours of starring at the
screen.

Many Thanks


--
PaulOakley
------------------------------------------------------------------------
PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
View this thread: http://www.excelforum.com/showthread...hreadid=386726