XL 2019 vba trouver un nombre manquant dans une suite donnee

[maguie]

Bonjour,
Je vous explique ma problématique. J'ai une suite de nombre (numéro de facture : 2018001) et je souhaiterais trouver les numéros de factures manquants dans une suite donnée avec un minimum et un maximum sachant que ce sont des grands nombres.
exemple de numéros : [2018001,2018002,20018004, 20180005..., 20180050]. Ma problématique est de trouver par exemple les numéros de facture manquants en précisant que le début est 20180001 et la fin 20180050. Et que la fonction vba doit rechercher ce nombre dans cet intervalle uniquement.
Par avance merci
Cordialement

 

[Phil69970]

Bonjour maguie, le forum

Je te propose ceci:
Je compare le N° avec le n° suivant -1 si les numéro sont égaux il y a pas de trou sinon il manque une facture ....

@Phil69970

 

[JHA]

Bonjour à tous,

Un début de piste

JHA

 

[maguie]

Bonjour,
Je vous remercie pour vos réponses mais j'aurais souhaité un code en vba sachant que la suite peut être désordonnée. Numéros issus d'un logiciel comptable.

 

[chris]

Bonjour à tous

Une solution PowerQuery, intégré à Excel, avec une liste dans le désordre, le choix des bornes et la liste des manquants

 

[maguie]

Merci pour la réponse mais pour PowerQuery cela fonctionne comment? Quelle est la procédure pour retourner les manquants?

 

[chris]

Re

Ouvre PowerQuery (Données, Obtenir des données, Lancer PowerQuery)

Tu verras les 2 requêtes :

• Choix qui récupère simplement la table de choix des bornes
• Factures qui récupères la table des factures et cherche les manquants : tu as les étapes à droite avec le détail dans la barre de formule et en cliquant sur le rouage

J'ai utilisé la technique des suites http://www.excel-formations.fr/Trucs_astuces/PQ05.php

Si l'une ou l'autre étape te pose question, reposte

 

[maguie]

Re

Ouvre PowerQuery (Données, Obtenir des données, Lancer PowerQuery)

Tu verras les 2 requêtes :

• Choix qui récupère simplement la table de choix des bornes
• Factures qui récupères la table des factures et cherche les manquants : tu as les étapes à droite avec le détail dans la barre de formule et en cliquant sur le rouage

J'ai utilisé la technique des suites http://www.excel-formations.fr/Trucs_astuces/PQ05.php

Super. Je vais essayer en soirée car là je pars travailler. Merci à vous tous et très bonne journée.

 

[job75]

Bonjour maguie, Phil69970, JHA, chris, le forum,

Voyez le fichier joint et cette formule matricielle en D3 :

=SIERREUR(PETITE.VALEUR(SI(NON(NB.SI(B:B;MIN(B:B)-1+LIGNE(INDIRECT("1:"&MAX(B:B)-MIN(B:B)+1))));MIN(B:B)-1+LIGNE(INDIRECT("1:"&MAX(B:B)-MIN(B:B)+1)));LIGNE(E1));"")

Elle fonctionne quelle que soit la manière dont le tableau est trié.

A+

 

[Phil69970]

Bonjour maguie, JHA, chris, job75, le forum

j'aurais souhaité un code en vba sachant que la suite peut être désordonnée.

Je te propose ce fichier :

Ma problématique est de trouver par exemple les numéros de facture manquants en précisant que le début est 20180001 et la fin 20180050

Tu indiques en E1 et E2 les numéros à rechercher...

@Phil69970

 

[job75]

Voyez le fichier joint et cette macro dans le code de Feuil1 (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, prem&, der&, mini&, maxi&, d As Object, resu(), tablo, i&, n&
Set P = [A1].CurrentRegion.Columns(2) 'à adapter
prem = Val([E2]): der = Val([E3]) 'à adapter
mini = Application.Min(P)
maxi = Application.Max(P)
prem = IIf(prem > mini, prem, mini)
der = IIf(der > maxi, maxi, der)
Set d = CreateObject("Scripting.Dictionary")
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
'---liste des numéros sans doublons---
For i = 2 To UBound(tablo)
    d(tablo(i, 1)) = ""
Next
'---liste des manquants---
ReDim resu(1 To UBound(tablo), 1 To 1)
For i = prem To der
    If Not d.exists(i) Then n = n + 1: resu(n, 1) = i
Next
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [E6] '1ère cellule de destination, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

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

A+

 

[maguie]

Voyez le fichier joint et cette macro dans le code de Feuil1 (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, prem&, der&, mini&, maxi&, d As Object, resu(), tablo, i&, n&
Set P = [A1].CurrentRegion.Columns(2) 'à adapter
prem = Val([E2]): der = Val([E3]) 'à adapter
mini = Application.Min(P)
maxi = Application.Max(P)
prem = IIf(prem > mini, prem, mini)
der = IIf(der > maxi, maxi, der)
Set d = CreateObject("Scripting.Dictionary")
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
'---liste des numéros sans doublons---
For i = 2 To UBound(tablo)
    d(tablo(i, 1)) = ""
Next
'---liste des manquants---
ReDim resu(1 To UBound(tablo), 1 To 1)
For i = prem To der
    If Not d.exists(i) Then n = n + 1: resu(n, 1) = i
Next
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [E6] '1ère cellule de destination, à adapter
    If n Then .Resize(n) = resu
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche quand on modifie ou valide une cellule quelconque.

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

A+

Bonjour.
Super!
Merci à vous tous pour votre contribution vous m'avez merveilleusement aidé.
ça fonctionne à merveille
Très bonne journée à vous tous. Et encore un grand merci pour votre aide.

 

[maguie]

Bonjour à tous

Une solution PowerQuery, intégré à Excel, avec une liste dans le désordre, le choix des bornes et la liste des manquants

Bonjour
J'ai regardé ce qui se faisait sur power pivot et powerQuerry. Il y a t-il des cours en ligne ou des informations sur le sujet?
Merci beaucoup

 

[patricktoulon]

bonjour
et ben dis donc ça en fait une machine pour si peu

Sub test()
Dim tablo, tbl(), i&, a&, mini&, maxi&
tablo = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
mini = WorksheetFunction.Large(tablo, UBound(tablo))
maxi = WorksheetFunction.Large(tablo, 1)
For i = mini To maxi
X = Application.IfError(Application.Match(i, tablo, 0), 0)
If X = 0 Then a = a + 1: ReDim Preserve tbl(1 To a): tbl(a) = "Il manque la facture N° " & i
Next
[B2].Resize(UBound(tbl)) = Application.Transpose(tbl)
End Sub

c'est pas compliqué tu a un min et un max ben boucle du min au max et teste avec match et c'est tout

 

[job75]

Bonjour le forum,

et teste avec match et c'est tout

Tu sais certainement que ça prend du temps sur un grand tableau, le Dictionary l'évite.

A+