Apr 20 2022 01:17 AM
Hi Guys,
first of all im not a native English so there might be some weird phrasing going on.
I would like to create a special Excel sheet. I am trying to avoid duplications in purchasing. The idea is that products with the same product characteristics (country, variety) cannot be purchased more than once in a period of less than 2 months. See screenshot with example "apples".
According to the logic, this would work like this: If identical product characteristics appear more than once in a period of less than two months, a "message" will appear. Example: red field.
If anyone would like to help me, I would be very happy! Kind regards
Apr 20 2022 02:09 AM
SolutionSelect A2:C4 (or however far down the data extend).
The active cell in the selection should be in row 2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,">="&EDATE($C2,-2),$C$2:$C$1000,"<="&EDATE($C2,2))>1
Adjust the ranges if needed; it doesn't matter if they extend further down than the data.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
Apr 20 2022 02:10 AM
The formula in German is
=ZÄHLENWENNS($A$2:$A$1000;$A2;$B$2:$B$1000;$B2;$C$2:$C$1000;">="&EDATUM($C2;-2);$C$2:$C$1000;"<="&EDATUM($C2;2))>1
Apr 20 2022 02:49 AM
@Hans Vogelaar thank you so so much for the help and the formula in german! I've got one problem though, the rows are not changing color. Do you know where the problem is?
Apr 20 2022 03:15 AM
I have attached the workbook I used to test the rule. Perhaps that will help.
If not: could you attach a sample workbook demonstrating the problem, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Apr 20 2022 03:22 AM
Apr 20 2022 02:09 AM
SolutionSelect A2:C4 (or however far down the data extend).
The active cell in the selection should be in row 2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,">="&EDATE($C2,-2),$C$2:$C$1000,"<="&EDATE($C2,2))>1
Adjust the ranges if needed; it doesn't matter if they extend further down than the data.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.