XL 2013 Rechercher des valeurs qui ne sont pas dans une colonne

P

Paul-Emile

XLDnaute Nouveau
Bonjour,
Je cherche une formule pour afficher des valeurs qui ne sont pas renseignées dans une colonne.
Je m'explique :
Sur la feuille 1 il y des immatriculations des véhicules tracteurs et citernes utilisés et je souhaite que sur la feuille 2 un résumé des immatriculations les véhicules citernes et tracteurs disponibles. Sur la feuille 2 j'ai toutes les immatriculations des véhicules du parc.
Merci pour votre aide.
Cordialement,
Paul-Émile.
 

Pièces jointes

  • Recherche données.xlsx
    12.7 KB · Affichages: 17
Solution
job75
Bonjour Paul-Emile,

Si vous voulez économiser du code placez dans ThisWorkbook :
VB: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name Like "Véhic. Dipo.*?" Then Workbook_SheetChange Sh, Sh.[A1] 'lance la macro
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Name Like "Véhic. Dipo.*?" Then Exit Sub
Dim jour$, F As Worksheet, FF As Worksheet, dest As Range, d As Object, col, colF%, colFF%, tablo, i&, x$, resu$(), n&
jour = Trim(Mid(Sh.Name, InStrRev(Sh.Name, ".") + 1)) 'Lundi Mardi etc...
Set F = Sheets(jour) 'véhicules utilisés
Set FF = Sheets("Base des données") 'immatriculations
Set dest = Sh.[A1] '1ère cellule du tableau des résultats
Application.ScreenUpdating =...
Trier par date Trier par votes
job75

job75

XLDnaute Barbatruc
Bonjour Paul-Emile, le forum,

Une solution VBA très classique dans le fichier joint :
VB: 
Private Sub Worksheet_Activate()
Worksheet_Change [A1] 'lance la macro
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim F As Worksheet, dest As Range, d As Object, col%, tablo, i&, x$, resu$(), n&
Set F = Feuil1 'CodeName
Set dest = [E1] '1ère cellule du tableau des résultats
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
dest(2).Resize(Rows.Count - dest.Row, 2).Delete xlUp 'RAZ
Set d = CreateObject("Scripting.Dictionary")
For col = 1 To 2
    tablo = F.Cells(1, col).Resize(F.Cells(F.Rows.Count, col).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    d.RemoveAll 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" Then d(x) = "" 'liste sans doublon
    Next i
    tablo = Cells(1, col).Resize(Cells(F.Rows.Count, col).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    ReDim resu(1 To UBound(tablo), 1 To 1)
    n = 0 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" And Not d.exists(x) Then n = n + 1: resu(n, 1) = x
    Next i
    '---restitution---
    If n Then dest(2, col).Resize(n) = resu
    dest.Resize(n + 1, 2).Borders.Weight = xlThin 'bordures
Next col
Application.EnableEvents = True 'réactive les évènements
End Sub
Le code est placé dans Feuil2, le tableau des résultats en colonnes E:F se met à jour quand on modifie ou valide une cellule quelconque ou qu'on active la feuille.

L'exécution est très rapide car on utilise des tableaux VBA et le Dictionary.

A+
 

Pièces jointes

  • Recherche données VBA(1).xlsm
    22.8 KB · Affichages: 3
Vote positif 1 Vote négatif
job75

job75

XLDnaute Barbatruc
Merci pour le fichier, voici le nouveau code adapté aux nouvelles colonnes traitées :
VB: 
Private Sub Worksheet_Activate()
Worksheet_Change [A1] 'lance la macro
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim F As Worksheet, FF As Worksheet, dest As Range, d As Object, col, colF%, colFF%, tablo, i&, x$, resu$(), n&
Set F = Sheets("Planning") 'véhicules utilisés
Set FF = Sheets("Base des données") 'immatriculations
Set dest = [A1] '1ère cellule du tableau des résultats
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
Set d = CreateObject("Scripting.Dictionary")
For Each col In Array(1, 3) 'colonnes A et C
    colF = IIf(col = 1, 14, 15) 'colonnes N et O
    colFF = IIf(col = 1, 5, 6) 'colonnes E et F
    tablo = F.Cells(1, colF).Resize(F.Cells(F.Rows.Count, colF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    d.RemoveAll 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" Then d(x) = "" 'liste sans doublon
    Next i
    tablo = FF.Cells(1, colFF).Resize(FF.Cells(FF.Rows.Count, colFF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    ReDim resu(1 To UBound(tablo), 1 To 1)
    n = 0 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" And Not d.exists(x) Then n = n + 1: resu(n, 1) = x
    Next i
    '---restitution---
    If n Then dest(2, col).Resize(n) = resu
    dest(1, col).Resize(n + 1).Borders.Weight = xlThin 'bordures
    dest(2, col).Offset(n).Resize(Rows.Count - n - dest.Row).Delete xlUp 'RAZ en dessous
Next col
Application.EnableEvents = True 'réactive les évènements
End Sub
 

Pièces jointes

  • Données(1).xlsm
    69.3 KB · Affichages: 4
Vote positif 1 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonjour Paul-Emile,

Si vous voulez économiser du code placez dans ThisWorkbook :
VB: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name Like "Véhic. Dipo.*?" Then Workbook_SheetChange Sh, Sh.[A1] 'lance la macro
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Name Like "Véhic. Dipo.*?" Then Exit Sub
Dim jour$, F As Worksheet, FF As Worksheet, dest As Range, d As Object, col, colF%, colFF%, tablo, i&, x$, resu$(), n&
jour = Trim(Mid(Sh.Name, InStrRev(Sh.Name, ".") + 1)) 'Lundi Mardi etc...
Set F = Sheets(jour) 'véhicules utilisés
Set FF = Sheets("Base des données") 'immatriculations
Set dest = Sh.[A1] '1ère cellule du tableau des résultats
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
If Sh.FilterMode Then Sh.ShowAllData 'si la feuille est filtrée
Set d = CreateObject("Scripting.Dictionary")
For Each col In Array(1, 3) 'colonnes A et C
    colF = IIf(col = 1, 14, 15) 'colonnes N et O
    colFF = IIf(col = 1, 5, 6) 'colonnes E et F
    tablo = F.Cells(1, colF).Resize(F.Cells(F.Rows.Count, colF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    d.RemoveAll 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" Then d(x) = "" 'liste sans doublon
    Next i
    tablo = FF.Cells(1, colFF).Resize(FF.Cells(FF.Rows.Count, colFF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
    ReDim resu(1 To UBound(tablo), 1 To 1)
    n = 0 'RAZ
    For i = 2 To UBound(tablo)
        x = tablo(i, 1)
        If x <> "" And Not d.exists(x) Then n = n + 1: resu(n, 1) = x
    Next i
    '---restitution---
    If n Then dest(2, col).Resize(n) = resu
    dest(1, col).Resize(n + 1).Borders.Weight = xlThin 'bordures
    dest(2, col).Offset(n).Resize(Rows.Count - n - dest.Row).Delete xlUp 'RAZ en dessous
Next col
Application.EnableEvents = True 'réactive les évènements
End Sub
Et effacez toutes les macros Worksheet_Activate et Worksheet_Change.

Chez moi le gain en nombres d'octets est peu important (6 Ko).

A+
 

Pièces jointes

  • Test Planning(1).xlsm
    208.6 KB · Affichages: 3
Vote positif 1 Vote négatif
Solution
P

Paul-Emile

XLDnaute Nouveau
haonv à dit:
Bonjour Paul-Emile, Gégé-45550,

Une autre solution pour éviter les cellules vides.

Cordialement
Cliquez pour agrandir...
Bonjour, haonv,

Est ce que tu pourras m'aider encore ?
J'ai adapté ta formule à mon ficher mais je n'ai pas le résultat voulu comme sue ton fichier.
J'ai rajouté sur la feuille "Véhicules Dispo." du fichier pour l'adaptation.
Merci par avance.
Paul-Émile.
 

Pièces jointes

  • Données.xlsx
    57.6 KB · Affichages: 2
Vote positif 0 Vote négatif
Gégé-45550

Gégé-45550

XLDnaute Accro
Paul-Emile à dit:
Bonjour, haonv,

Est ce que tu pourras m'aider encore ?
J'ai adapté ta formule à mon ficher mais je n'ai pas le résultat voulu comme sue ton fichier.
J'ai rajouté sur la feuille "Véhicules Dispo." du fichier pour l'adaptation.
Merci par avance.
Paul-Émile.
Cliquez pour agrandir...
Bonsoir,
Il est normal que vous n'obteniez pas le résultat escompté car la proposition de haonv nécessite de créer des champs nommés variables.
Dans le fichier qu'il a envoyé, ouvrez le gestionnaire de noms et étudiez la conception des noms de champs BD_Citerne, BD_Tracteur, Citerne F1 et TracteurF1.

La meilleure solution, rapide et élégante, est celle de Laurent78 mais elle nécessite Office365.
Cordialement,
 
Vote positif 0 Vote négatif
P

Paul-Emile

XLDnaute Nouveau
Gégé-45550 à dit:
Bonsoir,
Il est normal que vous n'obteniez pas le résultat escompté car la proposition de haonv nécessite de créer des champs nommés variables.
Dans le fichier qu'il a envoyé, ouvrez le gestionnaire de noms et étudiez la conception des noms de champs BD_Citerne, BD_Tracteur, Citerne F1 et TracteurF1.

La meilleure solution, rapide et élégante, est celle de Laurent78 mais elle nécessite Office365.
Cordialement,
Cliquez pour agrandir...
Gégé-45550
Merci j'ai modifier les noms de champs pour adapter au fichier. J'ai nommé, mais différemment les plages BD_Citerne, BD_Tracteur, CiterneF1 et TracteurF1.
Meric pour ton retour.
 
Vote positif 0 Vote négatif
P

Paul-Emile

XLDnaute Nouveau
haonv à dit:
Bonjour à tous,

Il est vrai que j'avais oublié de préciser qu'il fallait renommer des champs.
Mais tu as pu résoudre le problème grâce à l'intervention de Gégé, c'est le principal.👍

Cordialement
Cliquez pour agrandir...
Bonjour haonv,
J'ai renommé les champs mais il ne se passe rien quand je complète le tableau de la feuille "planning" (colonne E ou O).
Est ce que tu peut regarder ou est que j'ai pu faire l'erreur ?
Merci par avance !
Paul-Émile.
 

Pièces jointes

  • Données.xlsx
    57.6 KB · Affichages: 1
Vote positif 0 Vote négatif

Discussions similaires

B
  • Question
XL 2021 Créer liste déroulante contenant des liens hypertextes
Réponses
9
Affichages
477
job75
job75
A
  • Résolu(e)
XL 2021 Calcule consommation de carburant
Réponses
3
Affichages
368
ayoub456
A
C
  • Question
Microsoft 365 Liste déroulante en colonne 2 en fonction de la colonne 1
Réponses
9
Affichages
406
chris
chris
Max1300
  • Question
XL 2021 Dupliquer des champs en fonction d'un nombre sélectionné
Réponses
3
Affichages
177
gbinforme
G
R
  • Résolu(e)
Autres Masquer des lignes et colonnes en fonction d'une valeur d'un menu déroulant
Réponses
1
Affichages
141
Romain77144
R
P
  • Question
XL 2019 Bloquer la saisie dans une colonne sous condition
Réponses
1
Affichages
206
sylvanu
sylvanu
F
  • Résolu(e)
XL 2021 recherche doublons dans colonne puis suppression des lignes
Réponses
5
Affichages
200
ftpo69
F
C
  • Question
XL 2013 Userform ne fonctionne pas s'il manque des Numéros de liste
Réponses
9
Affichages
343
candido57
C
A
  • Résolu(e)
Microsoft 365 cumuler des valeurs de cellules dont l'emplacement peut varier dans des tableaux
Réponses
4
Affichages
250
auloin
A
Darkopsix
  • Résolu(e)
XL 2021 Récupérer des données de fichiers dons les noms sont renseignés dans des cellules
Réponses
2
Affichages
436
Darkopsix
Darkopsix

Membres actuellement en ligne

19 membres et 348 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 356
Messages
2 087 569
Membres
103 596
dernier inscrit
matthieu.devillers76