XL 2016 Signaler des bilocations du fait d'horaires identiques ou en chevauchement [selon le contenu de la colonne L du fichier]

[Webperegrino]

Bonjour Le Forum,

Après une journée de recherches infructueuses pour trouver la bonne formulation par codes Vba ou Formule, je viens vers vous avec l’exemple ci-joint.

Un tableau n° 4 me donne une synthèse de recherche de compagnons pour une personne, dans un tableau bien plus important (jours, lieux) : cela, j’arrive parfaitement à le résoudre avec du Vba.

Ce qui est présenté ici est un résultat de recherche pour le prénom SOLENN.
Mon problème :
Signaler en ROUGE ou en VIOLET les plages horaires en colonne (extraites de la colonne J-LIEUX) pour lesquelles, ici, SOLENN est en bilocation (sur deux lieux en même temps) ou plus hasardeux, - dans la recherche d’une solution vba ou autre formule - quand elle va se trouver avec une partie d’horaires en chevauchement sur deux lieux, ou plusieurs lieux.

Deux exemples de ma requête sont développés en colonne O), la coloration ayant été mise manuellement en colonne L.

Ainsi, par une invention de génie de votre part, des cellules « horaire » de la colonne L seraient :

• en rouge pour « Même horaire sur deux ou plusieurs endroits »
• en violet pour « Une partie de temps d’horaires, sur deux ou plusieurs endroits ».

Merci à l’avance pour votre contribution,

Webperegrino

 

[Lolote83]

Re bonjour,

- avec le segment de temps jaune plus long que le vert : NONOK Recouvrement TOTAL

Voir Cas n°3, non ?????
@+ Lolote83

 

[Webperegrino]

Autant pour moi, pardon.

 

[TooFatBoy]

=TEMPSVAL(GAUCHE(L19;5))+DROITE(L19;5) au format "13:30", c'est cela ?

Non, c'est juste :

=TEMPSVAL(GAUCHE(L19;5))

pour avoir l'horaire de début.

Et pareil pour avoir l'horaire de fin.

Mais puisque tu ne veux plus modifier ton tableau, ça ne sert plus à rien, sauf éventuellement dans des formules de test des tranches horaire.

 

[Webperegrino]

Voici où j'en suis en restant avec ma version de 2016 sans Power Query inconnu pour moi en utilisation.
Bons résultats d'approche avec colonnes O, P, Q, R, (TooFatBoy #18).

Ma formule en colonne S est mauvaise.
Les cas de Lolote83 en colonne U-V (j'ai colorié manuellement, malheureusement).

Fichier 1 : Colonne I non triée
Fichier 2 : Colonne I triée

 

[Webperegrino]

Je viens de reprendre le #19 avec corrections et ajout d'un deuxième fichier.

 

[Lolote83]

Bonjour,
Je continue mes recherches
@+ Lolote83

 

[Lolote83]

Re,
Dans ton dernier fichier transmis tu inscrit des recouvrement ou non, mais tu ne fais jamais appel à la personne concernée.
C'est la raison pour laquelle @JHA avec son PowerQuery avait de ton tableau initial établi un tableau structuré pour tester chaque personne.
Tu testes uniquement les horaires mais sans différencier qui est impacté.
Dans une même cellule tu peux avoir SOLENN qui sera effectivement en chevauchement mais pas NICOLLE pour la même cellule testée (Comment compte tu faire cette différence ?????)
Il faut que tu travailles en BDD comme le tableau fourni par @JHA
@+ Lolote83

 

[Webperegrino]

Lolote83,

J'avance un peu ! voir colonne [M] imparfaite.

Je viens te donner une peu d'explications sur le fonctionnement de mon application de construction du planning individuel de chaque bénévole.

En réalité, elle sert à mon épouse qui est responsable bénévole d'une équipe de 80 bénévoles à "répartir" sur plusieurs jours et plusieurs plages changeantes selon le jour.
Actuellement elle s'arrache les neurones à le faire sur papier...

Le très petit morceau de fichier placé ici est une simulation de situations imaginées. Tu as d'ailleurs bien trouvé tous les cas d'anomalies horaires.

Ici on n'est que dans le cas, par exemple de SOLENN pour la journée de demain et sur tous les sites sur lesquels on lui demandera d'opérer.

Les personnes qui accompagnent SOLENN sur ses postes, suite à une recherche pour elle seulement, se trouveraient en colonne [K] pour ce genre de résultat ; les autres prénoms sont donc là seulement pour indiquer à SOLENN avec qui elle travaillera.

Donc ce qui apparaît en colonnes I et L sont les POSTES DE SOLENN, sur une journée (il y a plusieurs journées étudiées).

L'objectif est donc de savoir quand SOLENN est à deux endroits en même temps et quels sont les postes pour lesquels elle va avoir une période de recouvrement, pour que je lui attribue des postes plus équilibrés en présence horaire ; ainsi le planning général sera modifié, SOLENN déplacée.

Une nouvelle recherche sur SOLENN aura corrigé les bilocations.
A mon épouse de savoir si on accepte des recouvrements ou pas : sur certains lieux, SOLENN peut quitter son binôme à l'approche de fin de prestation pour aller assurer l'autre poste avec un autre binôme, trinôme, etc.

Ensuite chaque autre personne de l'équipe, dont celle qui a l'occasion d'être avec SOLENN, aura aussi cette étude de "Bilocation-recouvrement".

Tu cites NICOLLE :
Ainsi NICOLLE aura son propre planning qui apparaîtra après une extraction macro-vba du planning général en construction ; comme j'aurai précédemment réglé le problème de bilocation au niveau du planning de SOLENN, NICOLLE sera en conflit horaire ou pas pour d'autres plages dans son propre planning individuel et pas nécessairement avec SOLENN, ce cas aura été corrigé au niveau de celui de SOLENN.

D'où ces indications d'horaires en rouge et en violet sur la première recherche chez Solenn.

Ainsi le planning général des horaires, qui sera attribué à 80 personnes sur 51 plages horaires (vaste programme !), aura moins d'anomalies de "bilocations-recouvrements" au fur et à mesure que je fais mes extractions, bénévole par bénévole, avec le petit tableau du pavé [I19:L34] comme résultat de "moulinette VBA".

 

[Webperegrino]

Je viens d'ajouter une info dans la colonne [M].
Je m'approche d'une solution, mais ce n'est pas encore cela...
Par VBa ce serait peut-être plus fort, notamment pour des comparaisons de cellules "horaires" non adjacentes ?

 

[piga25]

Bonjour,
Peut être en VBA

Sub ColorierNomsEtHoraires()
    Dim ws As Worksheet
    Dim derniereLigne As Long
    Dim i As Long, j As Long
    Dim noms1() As String, noms2() As String
    Dim k As Long, l As Long
    Dim horaire1 As String, horaire2 As String
    Dim debutHoraire1 As Date, finHoraire1 As Date
    Dim debutHoraire2 As Date, finHoraire2 As Date
    Dim nomTrouve As Boolean

    ' Définir la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Feuil1") ' Ajustez le nom de la feuille si nécessaire
    
    ' Trouver la dernière ligne avec des données dans la colonne K
    derniereLigne = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row

    ' Boucler à travers chaque paire de lignes pour vérifier les noms dupliqués et les horaires qui se chevauchent
    For i = 2 To derniereLigne - 1
        For j = i + 1 To derniereLigne
            ' Extraire les noms de chaque cellule dans la colonne K
            noms1 = Split(ws.Cells(i, 11).Value, "*") ' Colonne K
            noms2 = Split(ws.Cells(j, 11).Value, "*") ' Colonne K

            nomTrouve = False
            ' Comparer chaque mot de noms1 avec chaque mot de noms2
            For k = LBound(noms1) To UBound(noms1)
                For l = LBound(noms2) To UBound(noms2)
                    If Trim(noms1(k)) = Trim(noms2(l)) And Trim(noms1(k)) <> "" Then
                        nomTrouve = True
                        Exit For
                    End If
                Next l
                If nomTrouve Then Exit For
            Next k

            If nomTrouve Then
                horaire1 = ws.Cells(i, 12).Value ' Colonne L
                horaire2 = ws.Cells(j, 12).Value ' Colonne L

                ' Extraire les horaires de début et de fin
                debutHoraire1 = CDate(Left(horaire1, InStr(1, horaire1, " - ") - 1))
                finHoraire1 = CDate(Mid(horaire1, InStr(1, horaire1, " - ") + 3))
                debutHoraire2 = CDate(Left(horaire2, InStr(1, horaire2, " - ") - 1))
                finHoraire2 = CDate(Mid(horaire2, InStr(1, horaire2, " - ") + 3))

                ' Gérer les horaires se prolongeant jusqu'au lendemain
                If finHoraire1 < debutHoraire1 Then finHoraire1 = finHoraire1 + 1
                If finHoraire2 < debutHoraire2 Then finHoraire2 = finHoraire2 + 1

                ' Si les horaires sont identiques
                If horaire1 = horaire2 Then
                    ws.Cells(i, 11).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(i, 12).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(j, 11).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(j, 12).Interior.Color = RGB(255, 0, 0) ' Rouge

                ' Si les horaires se chevauchent
                ElseIf (debutHoraire1 < finHoraire2 And debutHoraire2 < finHoraire1) Then
                    ws.Cells(i, 11).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(i, 12).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(j, 11).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(j, 12).Interior.Color = RGB(148, 0, 211) ' Violet
                End If
            End If
        Next j
    Next i
End Sub

 

[Webperegrino]

... et encore plus près avec les propositions précédentes rassemblées dans la formule de la colonne [M], sans utiliser le "brouillon d'approche colonnes [O] à [V]...

 

[Webperegrino]

Bonsoir Piga25

(de B'sançon ? ma fille y ayant séjourné un moment)

Ah !
Ça a l'air de fonctionner ! Wouahh !
Je viens de l'appliquer sur le fichier #26, reproduit ici en pièce jointe.
J'ai l'impression que vous venez de trouver "LA" solution, mais mon épouse demande de venir manger ... alors je vais lever le verre à votre santé car ça s'arrose !
Je reviens vers vous après analyse de l'effet de la macro.
Merci, merci, merci.
Une épine en moins !
Bonne soirée
Webperegrino

 

[TooFatBoy]

Une prop par formules en pj.

 

[Webperegrino]

Piga25,
Je viens de placer les codes dans mon grand fichier source.

En réalité il est inutile de travailler sur la colonne des noms:
dans mon "fichier-source", Solenn est placée en [C2], la macro tourne et les prénoms des compagnons se placent, sans "Solenn" en fin de compte, dans la colonne [K].

Votre macro a juste besoin de travailler sur la colonne [L] pour l'étude de "bilocation-chevauchement" pour coloration ou pas.
J'ai d'ailleurs mis en sommeil la coloration sur la colonne [K]

Toutefois :
J'ai une erreur d'exécution 5 sur la ligne
debutHoraire1 = CDate(Left(horaire1, InStr(1, horaire1, " - ") - 1))
Savez-vous pourquoi ?
Meric

 

[Webperegrino]

Merci TooFatBoy,
Je ne serai pas libre avant 14 h 00 demain, j'étudie la proposition par formules #28.
Je viens de voir rapidement, ça a l'air d'être correct pour des analyses de lignes voisines.
Piga25 semble avec les codes VBA résoudre également les lignes non adjacentes pour déceler les horaires identiques ou à chevauchement.
Mais je n'ai pas regardé à fond, et surtout expérimenté dans le détail.
A demain et encore merci beaucoup pour vos aides à tous ici, ça fait vraiment plaisir.
Bonne soirée.