Détecter des enreg. qui se chevauchent en utilisant des formules

[Mongo]

Bonjour,

Je cherche à détecter des doublons dans des colonnes date, mais sans utilisation de Macros.
Je m'explique : dans un tableau utilisé pour gérer des réservations, la colonne B contient une date début et la colonne C une date de fin.
Je cherche, via des formules, à identifier les enregistrements dont les périodes se chevauchent, même si les dates début et fin ne sont pas identiques.
ex :
- enreg 1 : début 24/5/10 fin 30/5/10
- enreg 2 : début 5/6/10 fin 8/6/10
- enreg 3 : début 21/5/10 fin 26/5/10

(Ils ne sont volontairement pas classés par ordre chronologique car je souhaite réussir à faire apparaître ces doublons au moment de la saisie des réservations.)

Les enregistrements 1 et 3 se chevauchent. Je cherche donc à identifier ces enregistrements, et via un indicateur, utiliser la mise en forme conditionnelle pour qu'ils sautent aux yeux.

J'ai eu beau farfouiller dans les fonctions d'excel 2007 mais je ne trouve pas le moyen d'arriver à mes fins.

Merci aux pros qui voudront bien me donner un coup de main.

Pascal

 

[MJ13]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonjour Mongo

Merci aux pros qui voudront bien me donner un coup de main.

Je pense que les pros, dont je ne fais pas partie, auraient besoin d'un fichier .

 

[Mongo]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Effectivement, ce sera certainement mieux.
Dans le fichier joint, les enregistrements (23/12-26/12) et (24/12-25/12) doivent être détectés de même que (15/12-18/12) et (10/12-16/12).

D'avance merci

 

[MJ13]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonjour Mongo​

mais sans utilisation de Macros

Je laisse cela pour les pros des formules, mais cela me paraît complexe .

Sinon, tu peux tester cette macro: Alt+F11, Insertion d'un module, Copier le code dans le module, Alt+F8 puis Entrée.

Pour le test, je te conseille de retirer les MEFC.​

Sub Dates_Test_Chevauche()
'MJ
    derl = Range("C65536").End(xlUp).Row
    For lig = 2 To derl
        For p = 1 To (derl - 2)
            coldeb = 3
            Set a = Cells(lig, coldeb)
            Set b = Cells(lig, coldeb + 1)
            Set c = Cells(lig + p, coldeb)
            Set d = Cells(lig + p, coldeb + 1)
            If (a >= c And a <= d) Or (b >= c And b <= d) Then Range(a, b).Font.Color = RGB(255, 0, 0)
        Next
    Next
    For lig = derl To 2 Step -1
        For p = 1 To (lig - 2)
            coldeb = 3
            Set a = Cells(lig, coldeb)
            Set b = Cells(lig, coldeb + 1)
            Set c = Cells(lig - p, coldeb)
            Set d = Cells(lig - p, coldeb + 1)
            If (a >= c And a <= d) Or (b >= c And b <= d) Then Range(a, b).Font.Color = RGB(255, 0, 0)
        Next
    Next
End Sub

 

[CISCO]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonsoir

Cf une solution en pièce jointe.

Le défaut : Si tu as plusieurs correspondances, tu ne sais pas exactement laquelle correspond à laquelle (ex la ligne 10 avec les lignes 15 et 25, et la ligne 40 avec les lignes 45 et 46, ou la 10 avec la 15, la 25 et la 45 et la 40 avec la 46).

Je ne sais pas pourquoi, mais j'ai un problème avec la mise en forme. Lorsque je travaille dessus, pas de problème. Si je ferme le fichier, que je le réouvre, elle ne fonctionne plus : il faut que je "rentre" dans la fenêtre MFC, que je demande à modifier la règle, que je demande à l'appliquer, et après tout est OK...

J'ai expliqué en dessous du tableau la méthode utilisée.

Si c'est bon, mais je n'en suis pas sûr, la formule est vraiment simple.

A toi de tester et d'adapter.


@ plus.

 

[Mongo]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Merci à vous deux,

Tout d'abord MJ13. Ta macro fonctionne très bien... sauf qu'il n'est pas prévu de remise au format normal en cas de correction de la date en doublon. J'ai essayé d'ajouter cette remise au format initial mon mon faible niveau en VBA m'a rapidement bloqué.
Cependant, comme je l'avais précisé, je préfère les formules pour ce genre de contrôle car la mise en forme qu'on associe au résultat de la formule est instantané, sans avoir à déclencher une macro.

Donc Merci à toi aussi CISCO. J'ai adapté ta proposition de solution à mon problème en copiant ta formule en colonne Q et en créant une règle de MFC liée au résultat de la colonne Q. C'est nickel et le résultat correspond à ce que j'attends, à une exception près : je ne souhaite pas que les dates fin qui chevauchent des dates de début soient considérées comme des anomalies car des location peuvent se terminer à une date (matin) et une autre peut redémarrer le même jour (soir).
Je te joints ton fichier modifié pour le cas où tu aurais une solution.
Merci

 

[CISCO]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonsoir

Cf 2 propositions en pièces jointes, la (1) utilisant les résultats de la colonne Q, la (3) avec la formule directement dans la MCF.

Si quelqu'un pouvait m'expliquer pourquoi cette dernière mise en forme conditionnelle ne fonctionne que "sur ordre" (et pas automatiquement à l'ouverture), cela serait sympa.

Au passage, si on pouvait aussi me dire l'utilité de la case à cocher "interrompre si vrai"... merci d'avance.

@ plus

 

[Mongo]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Cisco, c'est super, ça correspond tout à fait à ce que je souhaitais.
J'ai choisi l'option formule en colonne Q et MFC liée au résultat de la formule en col Q.

Je ne saurais pas te dire pourquoi cela ne fonctionne que sur commande lorsque la formule est directement dans la MFC.

En revanche, pour ce qui est de la case "Interrompre si vrai", elle semble permettre de stopper la mise en forme conditionnelle dès qu'une MFC dont la case à cocher "Interrompre si vrai" est cochée, aura été traitée (les MFC sont traitées séquentiellement de haut en bas).
Si plusieurs MFC peuvent potentiellement être exécutées pour un même enregistrement, cette case permet de faire un choix parmi ces différentes possibilités plutôt que de laisser toutes les MFC s'exécuter et ainsi laisser la dernière MFC supplanter les autres.
Dans ce cas, l'importance de les ordonner correctement, cad selon la priorité des unes par rapport aux autres, est primordiale.

Encore merci et bon week-end

 

[CISCO]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonjour

Merci Mongo pour ton explication.

CI-joint une autre proposition, un petit peu plus simple, sur le même principe.

Je n'ai toujours pas trouvé pourquoi la MFC ne fonctionne pas directement dans le fichier 3.

@ plus

 

[MJ13]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonjour Mongo, Cisco

Cisco: Sur ce coup, je suis bien incapable de t'aider (pas très formule et MEFC surtout quand c'est complexe) .

Mongo: J'ai revu la macro qui présentait des problèmes sur certaines dates (voir pièce jointe). C'est une macro évènementielle sur le change dans la feuille.

 

[CISCO]

Re : Détecter des enreg. qui se chevauchent en utilisant des formules

Bonjour

Bonsoir

Si quelqu'un pouvait m'expliquer pourquoi cette dernière mise en forme conditionnelle ne fonctionne que "sur ordre" (et pas automatiquement à l'ouverture), cela serait sympa.

@ plus

Apparemment, le problème viendrait de l'enregistrement sur excel 2007. Sous excel 2003, la mise en forme conditionnelle utilisant "indirectement" un calcul matriciel fonctionne correctement (cf. pièce jointe).

@ plus