Compare two fields in two columns with a List, also two colums

[torti111]

Hello,

I need your help, urgently...

I have two fields B10 (month) and C10 (day). For example B10 = January and C10 = 02.
This is a special date required in this format and I have to check if the date is in a special List (A1:B106). In the List column A = month and B = day.
If the date is in the list, I need a feedback (mark double entry red or duplicate or something else)

Anybody have an idea?


Kind regards

Thorsten

[Mazzaropi]

torti111, Good morning.

You can use a Conditional Format.

A1:A10 --> Months
B1:B10 --> Days

C10 --> Month
D10 --> Day

Select cells you want to Colour
Conditional Format Menu --> Using formula
=AND($A1=$C$10,$B1=$D$10)
Select colour you prefer.
OK

Is that what you're looking for?
I hope it helps.

[torti111]

Hello Marcílio,

thank you for your help.
I don't have used Conditional Format before... Could you explain a litte bit more?
This is a part of the list:
Col A Col B
January 02
January 03
January 09
January 10
January 16
January 17
January 23
January 24
January 30
January 31

in and in another sheet
Col A Col B
January 16

if the 16 January is in the list (line 5), mark red or something else.

Many thanks

[Mazzaropi]

Thorsten, Good afternoon.

As you want to check for data in another TAB, the solution is a little different than I explained before.

Create Named Ranges using A1 and B1 of second tab.
LOOKMONTH --> =Plan2!$A$1
LOOKDAY --> =Plan2!$B$1

At Plan1:
Select A1:B10
Conditional Format Menu --> Using formula
Type this formula: =AND($A1=LOOKMONTH,$B1=LOOKDAY)
Format fill using colour of your preference.
OK

Take a look at this little example I did for you using this method.
http://speedy.sh/W5J6P/27-08-2015-Co...t-Test-OK.xlsx

Is that what you're looking for?
I hope it helps.

[torti111]

Good morning Marcílio,

thank you for your message. This is a good idea. But I think I need another solution because there are many datas to check.
I have uploaded a file, so you can see what I mean.
In the worksheet "Data" there are a lot of dates in column A and B. In worksheet "Dr" is a calendar, where I have to check, that no date from
worksheet "Data" column A and B is inside. If there is a date I have to change it manually to another (no weekend, no public holiday.....) For
this it will be good if it is marked in a colour or something else. Do you see a chance to solve my problem?
The file you can find here:
http://speedy.sh/FMwQ2/FileThorsten.xlsx

Best regard,

Thorsten

[Mazzaropi]

Thorsten, Good afternoon.

As I suggested at beginning, I'll use same logic to develop your new solution.

Named Ranges:
LookDay --> Data!C2:C107
LookMonthSP --> Data!A2:A107 (SPanish)
LookMonthEN --> Data!B2:B107 (ENglish)

Conditional Format:
DR! TAB
Rule 1
=SUMPRODUCT((LookMonthSP=B16)*(LookDay=C16))>0
Apply to:
=$B$16:$B$39;$D$16:$D$39;$F$16:$F$39;$H$16:$H$39
Format --> FIll = RED ; Font = BOLD/WHITE

Rule 2
=SUMPRODUCT((LookMonthSP=B16)*(LookDay=C16))>0
Apply to:
=$C$16:$C$39;$E$16:$E$39;$G$16:$G$39;$I$16:$I$39
Format --> FIll = RED ; Font = BOLD/WHITE

Take a look at this adaptation that I did in your file using this method.
http://speedy.sh/eAmhY/28-08-2015-Fi...estion-OK.xlsx

You can use this logic to apply to the others tabs if they have same layout.
Always, be careful with your ranges limits.
Adapt them as your necessity.

Is that what you're looking for?
I hope it helps.

[torti111]

Good morning Marcílio,

thanks for your help, that is a great solution. I used the Conditional Format in my file, but there is no date in red in the DR Tab.
Do you know why? I have uploaded the file here:

http://speedy.sh/68cPv/Calendar-Thorsten.xlsx

It would be greatfully if you can have a look to my file again.

Best regards,

Thorsten

[torti111]

Good morning Marcílio,

thanks for your help, that is a great solution. I used the Conditional Format in my file, but there is no date in red in the DR Tab.
Do you know why? I have uploaded the file here:

http://speedy.sh/68cPv/Calendar-Thorsten.xlsx

It would be greatfully if you can have a look to my file again.

Best regards,

Thorsten

[Mazzaropi]

Thorsten, Good evening.

Reasons for the appearance of errors:

a) The named ranges have been renamed the wrong way.

b) The new named ranges have been formatted incorrectly.

Because of these errors the formula in the conditional formatting lost comparison cells and pointed to the last cell of the last row of the Excel spreadsheet.

I recreated the named ranges again.
I fixed the formulas in conditional formatting.
Now they're back up and running again.

In the list of named ranges I did not erase the intervals that have been set wrongly.

This way it is possible to look at and see the difference between them.

Of course, the ranges that had been mistakenly renamed, now may already be deleted as they are not being used.

The formulas are simple, so there is no reason to complicate spreadsheets.

Here's your file updated.
http://speedy.sh/cVdGT/31-08-2015-Ca...-Thorsten.xlsx

I hope it helps.

[torti111]

Hi Marcílio,
thank you for your help. I have found my misstake (Strg + F3).

Kind regards,
Thorsten