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
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
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
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
P

Paul-Emile

XLDnaute Nouveau
job75 à dit:
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+
Cliquez pour agrandir...
Bonjour Job75
Merci pour ta proposition mais je ne matrice pas encre VBA. Mais je vais étudier cela par curiosité et peut je remplacerais les formules quand je comprendrais le fonctionnement.
Merci.
Paul-Émile.
 
Vote positif 0 Vote négatif
P

Paul-Emile

XLDnaute Nouveau
Paul-Emile à dit:
Bonjour Job75
Merci pour ta proposition mais je ne matrice pas encre VBA. Mais je vais étudier cela par curiosité et peut je remplacerais les formules quand je comprendrais le fonctionnement.
Merci.
Paul-Émile.
Cliquez pour agrandir...
J'ai essayé le fichier cela fonctionne très bien et ca me plait il ne reste plus que je maitrise la programmation pour adapter au fichier finale. Où, si tu le veut bien, je te transmet le ficher final pour la programmation ?
Merci encore .
Paul-Émile.
 
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
466
job75
job75
A
  • Résolu(e)
XL 2021 Calcule consommation de carburant
Réponses
3
Affichages
313
ayoub456
A
C
  • Question
Microsoft 365 Liste déroulante en colonne 2 en fonction de la colonne 1
Réponses
9
Affichages
389
chris
chris
Max1300
  • Question
XL 2021 Dupliquer des champs en fonction d'un nombre sélectionné
Réponses
3
Affichages
167
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
130
Romain77144
R
P
  • Question
XL 2019 Bloquer la saisie dans une colonne sous condition
Réponses
1
Affichages
188
sylvanu
sylvanu
F
  • Résolu(e)
XL 2021 recherche doublons dans colonne puis suppression des lignes
Réponses
5
Affichages
174
ftpo69
F
C
  • Question
XL 2013 Userform ne fonctionne pas s'il manque des Numéros de liste
Réponses
9
Affichages
324
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
211
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
420
Darkopsix
Darkopsix

Membres actuellement en ligne

11 membres et 142 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 202
Messages
2 086 177
Membres
103 152
dernier inscrit
Karibu