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

Paul-Emile

XLDnaute Nouveau
job75 à dit:
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
Cliquez pour agrandir...
Merci,
Il faut vraiment que j'étudie les macro pour la programmation.👍
Paul-Émile.
 
Vote positif 0 Vote négatif
H

haonv

XLDnaute Occasionnel
Bonjour à tous,

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 ?
Cliquez pour agrandir...
Puisque les données "tracteurs" par exemple sont déjà dans un tableau, ce n'est plus la peine de faire une liste qui s'ajuste aux données.
En A2 sur la feuille "Véhicules Dispo", par exemple, la formule devrait être:
VB: 
{=SIERREUR(INDEX(Tableau234;PETITE.VALEUR(NON(NB.SI(TracteurF1;Tableau234))*LIGNE(Tableau234);LIGNES($1:2)+NBVAL(TracteurF1)-1)-1);"")}
Par contre sur la feuille "planning" tu as des valeurs en N3 et N4, puis ensuite tu en as en N58 et N60.
Est-ce normal ?
Et tu as aussi une multitude de noms dans le gestionnaire....

Cordialement
 
Vote positif 0 Vote négatif
P

Paul-Emile

XLDnaute Nouveau
haonv à dit:
Bonjour à tous,


Puisque les données "tracteurs" par exemple sont déjà dans un tableau, ce n'est plus la peine de faire une liste qui s'ajuste aux données.
En A2 sur la feuille "Véhicules Dispo", par exemple, la formule devrait être:
VB: 
{=SIERREUR(INDEX(Tableau234;PETITE.VALEUR(NON(NB.SI(TracteurF1;Tableau234))*LIGNE(Tableau234);LIGNES($1:2)+NBVAL(TracteurF1)-1)-1);"")}
Par contre sur la feuille "planning" tu as des valeurs en N3 et N4, puis ensuite tu en as en N58 et N60.
Est-ce normal ?
Et tu as aussi une multitude de noms dans le gestionnaire....

Cordialement
Cliquez pour agrandir...
haonv,
c'était pour teste la formule que j'ai commencer a faire avant. Pour les multitudes de nom c'est pour les autres fonctions quand je vais remettre tous les autres données.
Bien cordialement,
Paul-Émile.
 
Vote positif 0 Vote négatif
P

Paul-Emile

XLDnaute Nouveau
job75 à dit:
Faites une copie du fichier réel, allégé et anonymisé, avec les bonnes colonnes à traiter.
Cliquez pour agrandir...
Bonjour, Job75
J'ai une question... J'aivais oublier que la feuille "planning" était ma base de travail pour la programmation.
J dois dupliqué la feuille "Planning" pour chaque jour de la semaine. du Lundi au samedi ainsi que la feuille "Véhicule dispo."
Est ce je dois crée un macro pour chaque feuille du jour de la semaine ?
Je te joint le fichier
Merci pour ta réponse.
Paul-Émile.
 

Pièces jointes

  • Test Planning.xlsm
    203.8 KB · Affichages: 3
Vote positif 0 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
job75 à dit:
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+
Cliquez pour agrandir...
Merci encore pour ton aide...Cela va me facilité ma tache quotidienne.
A+
PE
 
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
470
job75
job75
A
  • Résolu(e)
XL 2021 Calcule consommation de carburant
Réponses
3
Affichages
319
ayoub456
A
C
  • Question
Microsoft 365 Liste déroulante en colonne 2 en fonction de la colonne 1
Réponses
9
Affichages
393
chris
chris
Max1300
  • Question
XL 2021 Dupliquer des champs en fonction d'un nombre sélectionné
Réponses
3
Affichages
171
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
135
Romain77144
R
P
  • Question
XL 2019 Bloquer la saisie dans une colonne sous condition
Réponses
1
Affichages
193
sylvanu
sylvanu
F
  • Résolu(e)
XL 2021 recherche doublons dans colonne puis suppression des lignes
Réponses
5
Affichages
180
ftpo69
F
C
  • Question
XL 2013 Userform ne fonctionne pas s'il manque des Numéros de liste
Réponses
9
Affichages
328
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
215
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
426
Darkopsix
Darkopsix

Membres actuellement en ligne

3 membres et 50 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 215
Messages
2 086 330
Membres
103 186
dernier inscrit
Eliyass