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

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
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

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

Vote positif 1 Vote négatif
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
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
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
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

Vote positif 0 Vote négatif
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

Vote positif 1 Vote négatif
Solution
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
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

M
  • Question Question
Microsoft 365 Mon TCD ne prend pas en compte le filtre de la table auquel il est lié.
Réponses
3
Affichages
111
chris
chris
N
  • Question Question
Microsoft 365 Créer une fenêtre de vente qui alimentera un cumul dans un tableau
Réponses
2
Affichages
178
job75
job75
R
  • Question Question
XL pour MAC Ne pas afficher valeurs nulles dans un graphique
Réponses
5
Affichages
164
rtlv17
R
R
  • Question Question
Microsoft 365 aide recherche multiple entrées d'une cellule dans colonne
2
Réponses
16
Affichages
817
richard31
R
B
  • Question Question
XL 2019 Conserver les formules qui sont enregistrées dans des cellules
Réponses
8
Affichages
276
Phil69970
Phil69970
D
  • Résolu(e)
Microsoft 365 Assistance pour optimisation d'un code VBA qui ne fonctionne pas comme souhaité
2
Réponses
21
Affichages
465
desto
D
A
  • Question Question
Microsoft 365 Macro pour récupérer des colonnes qui ne sont pas conjointes
Réponses
4
Affichages
385
Anthony_51
A
D
  • Résolu(e)
Microsoft 365 recherche correspondance Excel
Réponses
7
Affichages
666
Did95
D
O
  • Résolu(e)
XL 2013 VBA/ Macro qui classe des données dans une colonne unique
Réponses
5
Affichages
106
onyirimba
O
richardlion
  • Question Question
XL 2016 copie de donnees horizontale en verticale
2
Réponses
25
Affichages
494
job75
job75
O
  • Question Question
XL 2013 Planification automatique des rdv d'intervention en fonction des dates disponibles
Réponses
3
Affichages
290
cathodique
cathodique
D
  • Résolu(e)
Microsoft 365 Remplissage colonne automatique dépendant d'un tableau
Réponses
7
Affichages
682
fanfan38
fanfan38
D
  • Question Question
XL 2019 Suppression doublons combinés entre deux cellules associées et présentation sous forme de tableau
Réponses
13
Affichages
654
alexga78
A
Y
  • Question Question
XL 2019 Masquer automatiquement des lignes selon la valeur d'une cellule ?
Réponses
2
Affichages
130
Yogean
Y
P
  • Question Question
XL 2016 Macro VBA remplissage tableau de synthèse % déchet ligne de production
Réponses
4
Affichages
178
vgendron
vgendron
A
  • Question Question
XL 2016 Paramétrage dans TCD qui a sauté
Réponses
6
Affichages
658
azraelle
A
T
  • Question Question
Microsoft 365 Afficher des résultats d'une colonne sur une ligne
Réponses
3
Affichages
213
toto142
T
C
  • Question Question
Microsoft 365 Disparation de données dans des fichiers excel
Réponses
10
Affichages
428
CedricR34
C
M
  • Question Question
XL 2021 Avoir des tableaux liés à une base de données
2
Réponses
24
Affichages
748
Nain porte quoi
N
C
  • Question Question
XL 2021 Cellules qui ne s'effacent pas sur formulaire et renvoi de valeurs du formulaire
Réponses
1
Affichages
494
gbinforme
G
Retour