Stuck

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

Your query is not clear, but with certain assumptions:

Your first table is in A1:B10, and the second in D1:E10, then use 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.

Mangesh

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" <PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com> wrote
in message news:PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com...
>
> 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 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.

--

HTH

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


"PaulOakley" <PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com> wrote
in message news:PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com...
>
> 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" <PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com> wrote
in message news:PaulOakley.1s3rqn_1121249191.0702@excelforum-nospam.com...
>
> 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
>

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

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" <PaulOakley.1s4086_1121260246.8271@excelforum-nospam.com> wrote
in message news:PaulOakley.1s4086_1121260246.8271@excelforum-nospam.com...
>
> 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" <PaulOakley.1s4086_1121260246.8271@excelforum-nospam.com> wrote
in message news:PaulOakley.1s4086_1121260246.8271@excelforum-nospam.com...
>
> 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
>

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" <PaulOakley.1s42us_1121263541.0448@excelforum-nospam.com> wrote
in message news:PaulOakley.1s42us_1121263541.0448@excelforum-nospam.com...
>
> 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
>

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" <PaulOakley.1s45n1_1121267202.9699@excelforum-nospam.com> wrote
in message news:PaulOakley.1s45n1_1121267202.9699@excelforum-nospam.com...
>
> 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" <PaulOakley.1s48eh_1121270761.1303@excelforum-nospam.com> wrote
in message news:PaulOakley.1s48eh_1121270761.1303@excelforum-nospam.com...
>
> 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
>