bonjour,

je voudrais savoir comment procéder en présence de deux bases de données sous Excel . L'ancienne contenant X entrées (classées par ordre alphabetique) et la nouvelle contenant Y entrées ( les éléments de l'ancienne base + des nouvelles entrées ) également classées par ordre alphabétique.

Pour ressortir uniquement les nouvelles entrées (soit Y-X).

merci.

Bonsoir,

Il faut un modèle d'organisation de tes bases, en particulier sur quel champ doit se faire la détermination des enregistrements de chacune (clé), et sous quelle forme tu souhaites matérialiser la détection.

Les enregistrements que tu cherches sont ceux qui dans la base Y n'ont pas de correspondances dans X. Appliqué à un champ (colonne dans Excel) définissant une clé unique pour chaque enregistrement, la fonction NB.SI appliqué au champ de X pour chaque élément du champ de Y donnera 1 pour les enregistrements communs à X et Y et 0 pour les enregistrements figurant uniquement dans Y. Voilà qui te donne la solution si tu penses pouvoir la mettre en oeuvre immédiatement.

bonsoir,

Pour plus de précisions

Je cherche à appliquer une fonction EXCEL à une colonne intitulée NOM

sachant que sur l'ancienne table dans la colonne NOM j'ai

DUPONT

DURAND

DUTOUR

et avec la nouvelle table

DELEPINE

DUPONT

DURAND

DUTOUR

DUVAL

Le but c'est de générer une troisième table avec dans la colonne NOM

DELEPINE

DUVAL

Je t'ai fourni une réponse... dans des termes comparables à tes "précisions".

Sa matérialisation exige que tu fasses ta part de boulot et fournisse un classeur à cette fin. Le modèle doit être de structure identique à ton classeur de travail, pour que la réponse soit parfaitement adaptée.

ferrand a écrit :

Sa matérialisation exige que tu fasses ta part de boulot et fournisse un classeur à cette fin.

bonjour,

Voici mon problème j'ai un mailing à envoyer à environ 200 nouvelles personnes, mais je ne sais pas qui sont ces 200 personnes car on m'a fourni un fichier de BDD Excel de 1500 personnes où les anciens et les nouveaux sont mélangés.

J'ai donc un classeur avec en feuille1 1300 personnes (les anciens) et en feuille2 1500 personnes(anciens + nouveaux) .Je voudrai en feuille3 générer une table avec uniquement les 200 nouvelles personnes en comparant la table de la feuille1 et la table de la feuille2.

Sachant que chaque personne est affecté d' un numéro de carte d'adherent UNIQUE. C'est donc sur la colonne contenant le numero de carte qu'il faut que je travaille.

Mais quelles fonctions utiliser ?

Bonjour,

Si tu as une date (d'inscription, achat, appel, ...) tu peux trier dessus. Un copier coller suffit ensuite.

A+

Bonsoir,

J'ai donc un classeur avec en feuille1 1300 personnes (les anciens) et en feuille2 1500 personnes(anciens + nouveaux) .Je voudrai en feuille3 générer une table avec uniquement les 200 nouvelles personnes en comparant la table de la feuille1 et la table de la feuille2.

On avance un peu ! On sait déjà qu'il y a une base par feuille, il reste plus qu'à savoir comment elles sont réparties... Les formules ne se mettent pas en l'air mais dans des feuilles, en utilisant des références dans lesdites feuilles. C'est d'ailleurs pareil pour les macros, il faut qu'elles s'appliquent à quelque chose... Avec un modèle reprenant la même structure de ton classeur de travail, avec un échantillon de données (30 à 50 pour tester significativement), fictives ou non mais de même type que tes données réelles, tu aurais déjà fait ton extraction.

Sachant que chaque personne est affecté d' un numéro de carte d'adherent UNIQUE. C'est donc sur la colonne contenant le numero de carte qu'il faut que je travaille.

Excellente initiative ! Parce qu'effectivement le Nom n'est jamais assimilable à une clé primaire. Cela aurait pu compliquer.

Mais quelles fonctions utiliser ?

Formule matricielle (bien sûr ), qui utilise des fonctions classiques comme NB.SI (pour la détection), PETITE.VALEUR (pour une récupération ordonnée, ou GRANDE.VALEUR), INDEX (pour l'extraction)... J'aurais deux variantes en fait, selon ce qui s'avèrera le plus commode en situation.

On peut aussi opérer avec une macro. De toutes façons c'est une manoeuvre temporaire.

A suivre...

Il semble que tu n'entends pas donner suite à ta demande. Bonne continuation.

Je n'ai toujours pas compris comment mettre en œuvre ta methode.

Il n'y a pourtant rien à comprendre :

Avec un modèle reprenant la même structure de ton classeur de travail, avec un échantillon de données (30 à 50 pour tester significativement), fictives ou non mais de même type que tes données réelles,

Une fois que tu l'as établi et communiqué par cjoint, je pourrai matérialiser la solution.

bonjour,

Comment fonctionne le cjoint pour mettre un fichier à disposition des forumeurs ?

http://www.cjoint.com

Tu cliques sur Parcourir pour aller chercher ton fichier

Tu cliques ensuite sur Créer le lien Cjoint

Une fois le lien créé, tu le copies, puis tu le colles dans un post sur le forum.

bonsoir,

Voici le lien vers mon fichier BDD Excel

http://cjoint.com/?0ChrQGjXUT5

A partir de la totalité des clients 2013 (feuille1) et de la totalité des clients 2014 (feuille2) , je voudrai avoir en feuille3 (nouveaux) uniquement les nouveaux clients entrés en 2014.

Pour eviter le problème des doublons sur les noms , les formules doivent porter sur la colonne qui contient le numero de client.

Vu, Je te fais ça après manger.

Le numéro de carte est unique, ta base ne comporte que 4 champs.

A+

merci.

oui le numero de client est unique et il y a 4 champs.

Si tu peux ensuite m'expliquer, un peu, le principe des formules matricielles.

Re,

1re étape : Nommer les bases ancienne et nouvelle, et nommer les colonnes contenant le numéro de carte. Ce n'est pas absolument indispensable mais cela facilite l'écriture des formules...

J'ai nommé : BasAnc, BasNou, et NumBA, NumBN. Pas besoin d'indiquer à quoi chaque nom se réfère. Les noms prennent en compte les plages de données, sans la ligne d'en-tête.

J'ai mis des noms dynamiques (peuvent s'adapter aux variations de plages, utilisent la fonction DECALER pour définir la référence de la plage nommée). Ce n'était pas non plus nécessaire, dans la mesure où l'opération répond à un besoin temporaire, mais la méthode peut toujours être réutilisée.

J'ai nommé les bases de la façon habituelle pour affecter des noms dynamiques, et nommé les colonnes en décalant la définition des bases. L'inverse était également possible. Souci inconscient de symétrie dans cette opération, car BasAnc n'est pas utilisé dans les formules.

2e étape : Formulation. J'avais évoqué 2 variantes, la première consistait à extraire directement le numéro dans la feuille nouveaux clients, s'il avait été situé en première colonne. Les autres éléments étant ensuite extraits en utilisant ce numéro avec RECHERCHEV.

Le numéro étant situé en 3e colonne, on ne va donc pas l'extraire directement mais calculer l'index de ligne dans une autre colonne, l'ensemble des données étant alors extrait en utilisant la fonction INDEX.

3 formules sont utilisées :

1) Calcul du nombre d'enregistrement de la base nouvelle ne figurant pas dans l'ancienne :

=SOMMEPROD(--(NB.SI(NumBA;NumBN)=0))

La fonction NB.SI va calculer pour chaque élément de NumBN le nombre de fois où il figure dans NumBA. Ce ne peut être que 1 (éléments anciens) ou 0 (éléments nouveaux ne figurant pas parmi les anciens). Elle va donc renvoyer une suite de 0 et de 1. En la mettant sous condition (=0), les 0 seront transformés en VRAI et les 1 en FAUX. Le -- figurant devant cette expression mise entre parenthèses permet de transformer les VRAI en 1 et les FAUX en 0. SOMMEPROD peut alors en faire l'évaluation en additionnant les 1 (qui correspondent aux éléments nouveaux).

SOMMEPROD étant une fonction matricielle dans son utilisation normale, cela dispense d'une validation matricielle malgré l'utilisation d'une matrice (au lieu d'une valeur unique) en 2e argument de NB.SI.

J'ai placé cette formule en F1. Elle peut être placée où on veut. Ce calcul n'est pas obsolument indispensable, il permet de mettre les autres formules sous conditions pour éviter l'affichage de valeurs d'erreurs. Si on veut s'en dispenser, il suffit de stopper la recopie par tirage des formules à l'apparition de valeur d'erreur (et d'effacer la ligne en erreur (c'est qu'on aura dépassé le nombre de nouveaux éléments à extraire).

Si l'on est sûr de ses bases, on pouvait également obtenir le nombre d'éléments à extraire par la différence du nombre de lignes entre la nouvelle base et l'ancienne.

.../...

2) Calcul de l'index de ligne des éléments nouveaux à extraire :

J'ai mis cette formule en colonne F à partir de F2. La colonne est indifférente, mais on doit partir de la première ligne de données de la base extraite, donc la 2.

En F2 : {=SI(LIGNE(1:1)<=$F$1;PETITE.VALEUR(SI(NB.SI(NumBA;NumBN);9^9;LIGNE(NumBN)-1);LIGNE(1:1));"")}

Cette formule est matricielle et doit donc être validée par la combinaison de touches Ctrl+Maj+Entrée, ce qui a pour effet de la faire encadrer par des accolades.

Comme précédemment, la fonction NB.SI va nous renvoyer une matrice de 0 (élément nouveaux) et de 1. Mise en argument conditionnel d'une fonction SI, les 0 seront des FAUX et les 1 des VRAI (ce qui dispense de mettre "=1" ou ">0" pour obtenir ces VRAI ou FAUX). Si VRAI, on renvoie une valeur asseez grande pour être supérieure au nombre de ligne de la base (avec 9^9, on a de la marge !), si FAUX on renvoie le numéro de ligne de l'élément (on retranche 1, pour avoir l'index de ligne dans la base, les données commençant ligne 2).

On a donc une matrice dans laquelle les index de ligne qu'on recherche sont mélangés à des valeurs nettement supérieures à l'index le plus élevé. Il faut donc en quelque sorte le filtrer pour extraire les index à la suite. La fonction PETITE.VALEUR appliquée à cette matrice va nous permettre de mettre ses valeurs dans l'ordre à partir de la plus petite. L'argument 'rang' de la fonction est indiquée par : LIGNE(1:1), qui va renvoyer 1, mais qui deviendra LIGNE(2:2) lors de la recopie ligne suivante (renvoyant donc 2), et ainsi de suite. En tirant la formule pour la recopier, on va donc incrémenter l'argument de la fonction et extraire tous les index recherchés à la suite.

La mise sous condition de cette formule aboutit à ce qu'elle n'affiche plus rien lorsqu'on dépasse le nombre d'éléments à extraire que l'on a précédemment calculé.

3) Extraction des données :

Formule en A2 : =SI($F2<>"";INDEX(BasNou;$F2;COLONNE());"")

Utilisation classique de la fonction INDEX, ligne étant fourni par la formule précédente en F et la colonne étant la même...

On tire latéralement sur la ligne cette formule, puis on tire l'ensemble vers le bas, elle sera ainsi rapidement recopiée sur l'ensemble de la base à extraire.

Voilà pour la méthode formules : http://cjoint.com/?EChviXln3SY

A noter que la présence de formule aboutit à rendre ton extraction dépendante des modifications affectant les bases nouvelle et ancienne auxquelles se réfèrent les formules (si tu fais des tris sur ces bases par exemple). Si tu souhaites éviter des modifications en touchant aux bases de référence, il faut la rendre autonome en la copiant et collant en valeurs sur elle-même, ce qui supprimera les formules.

La méthode macro évite cet aspect en produisant d'emblée des valeurs (pas de formules).

Mais pour l'instant, pause et je regarde un film.

bonsoir,

merci, j'ai vu que ta methode fonctionne très bien.

Malheureusement je ne comprend rien et franchement ça m'ennuie car pour l'adapter à une BDD de plus de 1500 noms je ne sais pas si je vais y arriver.

Je ne vois pas du tout pourquoi tu fais intervenir 9 ^9 , par exemple (??)

N'y-a-t-il pas plus simple ? En écrivant un module en VBA, peut-être ?

Je crois avoir fourni des explications particulièrement détaillées sur les formules pour permettre de comprendre la façon dont elles fonctionnent. A toi d'étudier la chose pour progresser. En outre, l'utilisation de noms de plages te permet de les utiliser par simple copie sans aucune adaptation (si tu affectes les mêmes noms aux plages considérées) ou en n'ayant que les noms à modifier (si tu en choisis d'autres), en les collant aux emplacements correspondants de ton fichier.

9^9 (sans espace !) est un nombre (9 puissance 9). Tu tapes =9^9 dans une cellule, tu auras le résultat. On a coutume de l'utiliser lorsqu'on a besoin d'un grand nombre, supérieur à tous les nombres susceptibles d'être utilisés dans les données qu'on traite. Une feuille Excel dépassant un peu 1 million de lignes, on est sûr de se situer au-dessus ! Cette valeur étant affectée aux éléments qu'on ne recherche pas, en listant les valeurs de la matrice dans l'ordre croissant, on aura tous les éléments recherchés avant les autres...

Une macro a l'avantage de fournir une nouvelle base indépendante de celles dont elle procède, et donc immédiatement utilisable, et sans les ajouts sur la feuille destinés à la construire avec des formules. Comme adaptation, elle réclamera les mêmes que la méthode formules : nommer les plages.

J'ai ajouté un nom de plage (par rapport à la version formules) : NvB qui représente la cellule A2 de la base Nouveaux éléments. Il fallait une cible pour affecter les résultats trouvés par la macro.

Ce que fait la macro :

- Elle trie les bases ancienne et nouvelle sur le numéro client.

- Elle dimensionne un tableau sur le nombre de colonnes des bases et la différence de lignes entre nouvelle et ancienne (pour accueillir les données à extraire).

- Elle parcourt les numéros de la nouvelle base et lorsque ce dernier ne figure pas dans l'ancienne, elle inscrit les données de la ligne dans le tableau.

- Elle transfère les données du tableau sur l'emplacement des nouveaux éléments.

- Elle retrie les bases et les nouveaux éléments sur le nom.

Mécanisme simpliste plus facile à appréhender que le fonctionnement des formules matricielles, mais c'est souvent que les macros apparaissent plus simples que l'équivalent formules pour obtenir le même résultat.

Sub ExtracNouvo()
Dim i%, j%, h%, k%, n%, p%, Nv()
[BasAnc].Sort key1:=[NumBa], order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[NumBN], order1:=xlAscending, Header:=xlNo
k = [BasNou].Columns.Count
n = [BasNou].Rows.Count
p = [BasAnc].Rows.Count
ReDim Nv(k - 1, n - p - 1)
With [BasNou]
For i = 1 To n
If [NumBN].Cells(i, 1).Value <> [NumBa].Cells(i - h, 1).Value Then
h = h + 1
For j = 1 To k
Nv(j - 1, h - 1) = [BasNou].Cells(i, j).Value
Next j
End If
Next i
End With
With [NvB]
For i = 1 To n - p
For j = 1 To k
.Cells(i, j).Value = Nv(j - 1, i - 1)
Next j
Next i
End With
[BasAnc].Sort key1:=[BasAnc].Cells(1, 1), order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[BasNou].Cells(1, 1), order1:=xlAscending, Header:=xlNo
Range([NvB].Cells(1, 1), [NvB].Cells(n - p, k)).Sort key1:=[NvB].Cells(1, 1), _
order1:=xlAscending, Header:=xlNo
End Sub

Plus qu'à tester : http://cjoint.com/?ECiacEZpJQO

bonjour,

merci pour la version utilisant VBA.

Mais test pas possible car avec le lien de telechargement que tu me donnes dans ton dernier message, tu ne m'as pas envoyé les bases de données avec la macro..... mais juste mon fichier Excel d'origine.

En ce qui me concerne lire des lignes de programme en VBA (dérivé du Basic) est nettement plus compréhensible que les formules Excel !

Où je bloque, ce n'est pas tant sur ce que fait une fonction dans une formule , (il suffit de lire la définition); mais c'est lorsqu'il y a plusieurs fonctions imbriquées les unes dans les autres avec des multiples parenthèses que je n'arrive plus à suivre.

salutations cordiales

Tu as mal regardé ! Le fichier n'est plus .xlsx mais .xlsm (parce qu'il contient une macro). A l'ouverture, Excel te demande si tu veux activer les macros. Si tu les actives, le fichier est prêt à tester : onglet Développeur > Macros > Tu exécutes la macro ExtracNouvo et tu observes le résultat...

Au temps pour moi.

j'avais beau appuyer sur Alt F11 , je ne voyais aucune macro apparaitre ,mais c'est parce qu'Excel bloquait l'execution des macros.

Bonjour,

Pourquoi commences-tu à trier chacune des deux bases selon le numero de client ?

ça ne me parait pas indispensable pour rechercher avec une boucle si un numero est present ou pas dans l'ancienne base .

D'autre part pour comprendre ta routine VBA pour ce tri je me suis documenté sur

https://msdn.microsoft.com/fr-fr/library/office/ff840646.aspx

Mais n'y a -til pas une formule plus simple ?

Pourquoi commences-tu à trier chacune des deux bases selon le numero de client ?

La méthode utilisée exige que les deux bases soient identiquement ordonnées, ce qui ne peut être garanti qu'en les triant sur la clé primaire.

ça ne me parait pas indispensable pour rechercher avec une boucle si un numero est present ou pas dans l'ancienne base .

Certes ! Mais ce n'est pas ce que je fais.

D'autre part pour comprendre ta routine VBA pour ce tri je me suis documenté sur

https://msdn.microsoft.com/fr-fr/library/office/ff840646.aspx

Ce n'est pas ma routine, je me contente de l'utiliser comme méthode d'un objet Excel sans avoir accès à son implémentation, et elle appartient à la bibliothèque Excel, non à VBA.

Tu aurais trouvé la même chose dans l'aide, sans avoir à chercher si loin.

Mais n'y a -til pas une formule plus simple ?

Je ne comprends pas ce que tu veux dire par là. S'il s'agit du tri, il ne prend qu'une ligne de code... Si tu parles de l'ensemble de la procédure, on peut faire plus long (avec une boucle pour rechercher si chaque numéro de la nouvelle base est présent dans l'ancienne, par exemple ), et les autres variantes que je peux imaginer ne me paraissent ni plus rapides, ni plus simples à écrire. Mais je demeure ouvert à toute suggestion si tu as une idée...

NB- Le tri final n'a rien d'indispensable pour répondre au problème posé initialement. Il est d'ailleurs incomplet, réalisé sur le nom seulement (au lieu du nom et prénom habituels en la matière)...

N'y a -t-il pas moyen avec VBA d'ecrire une procédure avec des boucles classiques, qui prend le premier n° de client de la première ligne dans la nouvelle base (clients2014) et de voir si ce numero de client est présent dans l'ancienne base (clients2013) en parcourant toutes les lignes de l'ancienne base.

- si il est présent, on passe à la ligne suivante (deuxième numero de client de la nouvelle base ) et on recommence

-s'il n'est pas présent , on transfère l'ensemble de la ligne dans la feuille3 (nouveauxx clients)

une fois qu'on a parcouru toutes les lignes de la feuille clients2014 on arrête .On aura bien nos nouveaux clients dans la feuille3

Je te suggère de l'écrire ! Tu n'oublieras pas une fois fait de comparer le nombre de lignes de code et de mesurer les durées d'exécution respectives.

Par ailleurs, pourrais-tu indiquer ce qu'est pour toi une boucle classique ou non classique... Je ne connais pas plus classique que la boucle For... Next et je n'ai pas utilisé autre chose. Au demeurant cet aspect est sans incidence sur l'adéquation à l'action effectuée.

Ton propos me laisse penser que tu n'as pas compris ce que fait précisément la macro. Cela ne l'empêche pas de fonctionner efficacement certes, mais si tu tiens vraiment à décortiquer le mécanisme je t'encourage à la relire plus attentivement pour discerner en quoi la méthodologie utilisée diffère de ta proposition.

il y a une chose que je ne comprend pas c'est comment tu as procédé pour nommer les bases.

Comment se fait-il que dans le gestionnaire de noms quand on visualise la réference de chaque nom que tu as donné , il y a une formule avec la fonction DECALER et la fonction NBVAL qui y figurent ?

Moi lorsque je selectionne l'ensemble des lignes et colonnes, et que je donne un nom à cette plage de cellules, je n'ai pas la même reference, j'ai juste première cellule..dernière cellule !

Comment se fait-il que dans le gestionnaire de noms quand on visualise la réference de chaque nom que tu as donné , il y a une formule avec la fonction DECALER et la fonction NBVAL qui y figurent ?

La fonction DECALER permet de nommer une plage dynamiquement, c'est à dire que le nom s'adapte aux variations de la plage quand le nombre de lignes augmente.

La syntaxe de la fonction DECALER(Réf.;décal.Ligne;décal.Col.;Nbdelignes;Nbdecolonnes).

On définit la plage à partir de sa cellule supérieure gauche (naturellement fixe). Le décalage est donc nul. Le nombre de lignes est calculé par NBVAL appliqué à une colonne dont on est sûr que toutes les cellules seront servies (on retranche 1 s'il y a une ligne d'en-tête non incluse dans la plage définie). Le nombre de colonnes étant en général invariant, on le porte en valeur (s'il devait également varier on applique la même méthode que pour les lignes, sur une ligne permettant cette évaluation).

NB- Pour ton opération d'extraction, ponctuelle par définition, il n'est pas utile d'avoir recours à un nom dynamique, cependant pour un usage durable, il est pratique d'avoir des noms s'adaptant aux variations sans avoir à les retoucher à chaque modification.

ferrand a écrit :

La fonction DECALER permet de nommer une plage dynamiquement, c'est à dire que le nom s'adapte aux variations de la plage quand le nombre de lignes augmente.

La syntaxe de la fonction DECALER(Réf.;décal.Ligne;décal.Col.;Nbdelignes;Nbdecolonnes).

bonjour,

Donc si je veux nommer dynamiquement comment faire ?

Est-ce qu'il faut selectionner la plage et donner un nom comme pour nommer statiquement , puis ensuite aller dans le gestionnaire de noms et rentrer manuellement la formule (dans le champ reference à) à la place de la reference statique qu'Excel a proposé pour la plage de cellules ?

ou

procéder différemment. ?

D'un point de vue pratique, tu sélectionnes la cellule supérieure gauche de la plage à nommer. En cliquant sur sur Définir un nom, tu obtiendras dans la boîte de dialogue Nouveau nom dans la zone Fait référence à (par exemple) :

=Feuil1!$A$2

Tu mets le nom choisi dans la zone Nom puis tu te positionnes après le signe = pour taper la formule :

=decaler(Feuil1!$A$2;;;nbval(

A ce stade tu vas sélectionner dans la feuille la colonne retenue pour l'évaluation des colonnes (clic sur la lettre de colonne) :

=decaler(Feuil1!$A$2;;;nbval(Feuil1!$A:$A

puis tu poursuis la frappe de la formule : =decaler(Feuil1!$A$2;;;nbval(Feuil1!$A:$A);4)

Et tu valides. Tu auras ceci en vérifiant dans le gestionnaire de noms : =DECALER(Feuil1!$A$2;;;NBVAL(Feuil1!$A:$A);4)

merci.

bon, je crois qu'on a fait le tour de la question.

Oui ! Sans doute un peu plus même. Bonne continuation.

@ferrand

Bonjour,

Je reviens vers vous par rapport à la macro VB que vous m'avez donné.

Celle-ci fonctionne parfaitement bien mais j'ai un petit souci avec une autre base de données qu'on m'a donné car le problème à résoudre est un tout petit peu différent et il faudraitt modifier la macro sinon on a une erreur d'execution.

Votre macro fonctionne si tous les enregistrements de l'ancienne base se retrouvent dans la nouvelle base mais s'il en manque seulement un , elle ne fonctionne plus.

la macro s'arrête sur la ligne

Nv(j - 1, h - 1) = [nouvbase].Cells(i, j).Value

Comment faire pour la modifier en conséquence car j'ai des gens qui se sont désinscrits, qui figuraient dans l'ancienne base mais ne sont plus dans la nouvelle.

Sub Extraire_nouveaux()
Dim i%, j%, h%, k%, n%, p%, Nv()
' tri des anciennes et nouvelles bases de données sur numero de client
[ancbase].Sort key1:=[NumBA], order1:=xlAscending, Header:=xlNo
[nouvbase].Sort key1:=[NumBN], order1:=xlAscending, Header:=xlNo
k = [nouvbase].Columns.Count
n = [nouvbase].Rows.Count
p = [ancbase].Rows.Count
h = 0
' dimensionnement tableau pour nouveaux
ReDim Nv(k - 1, n - p - 1)
' parcourir nouvelles données et mise en tableau elements nouveaux
With [nouvbase]
For i = 1 To n
If [NumBN].Cells(i, 1).Value <> [NumBA].Cells(i - h, 1).Value Then
h = h + 1
For j = 1 To k
Nv(j - 1, h - 1) = [nouvbase].Cells(i, j).Value
Next j
End If
Next i
End With
With [NvB]
For i = 1 To n - p
For j = 1 To k
.Cells(i, j).Value = Nv(j - 1, i - 1)
Next j
Next i
End With
' nouveau tri des données
[ancbase].Sort key1:=[ancbase].Cells(1, 1), order1:=xlAscending, Header:=xlNo
[nouvbase].Sort key1:=[nouvbase].Cells(1, 1), order1:=xlAscending, Header:=xlNo
Range([NvB].Cells(1, 1), [NvB].Cells(n - p, k)).Sort key1:=[NvB].Cells(1, 1), _
order1:=xlAscending, Header:=xlNo
End Sub

En effet, c'est plus le même problème ! Je pense qu'il est préférable de la réécrire pour cette nouvelle situation...

Je vois ça après mes courses alimentaires, qui n'attendrons pas !

A+

Dans la situation antérieure, il était acquis que la nouvelle base avait plus d'enregistrement que l'ancienne. Dès lors qu'il y a des sorties entre les deux, ce n'est plus garanti. Il faut donc compter les sorties repérées en plus des entrées, et tester que l'on n'a pas atteint la fin de l'ancienne base.

La nouvelle macro va conserver les mêmes déclarations de variables, + une variable Integer (s pour compter les sorties). Là je modifie en peu le tableau utilisé pour les comparaisons, car on ne sait plus au départ quel sera le nombre de nouvelles entrées à extraire. Et il n'est plus intéressant de constituer le tableau pour recueillier toutes les données à transférer comme précédemment, il faudrait le faire par ajout de lignes une par une. Je me contente donc d'un tableau à 1 dimension, dimensionné sur la longueur de la nouvelle base, dans lequel je recueille les valeur du champ clé primaire de la nouvelle base. On a dans ce tableau correspondance des indices avec les lignes de la nouvelle base, ce qu'on pourra utiliser lors du transfert.

La boucle de comparaison, va défiler les valeurs du tableau (soit celles de la nouvelle base) : la ligne i de la nouvelle base est comparée à la ligne i+s-h de l'ancienne (ligne augmentée des sorties mais diminuée des entrées nouvelles). Si les valeurs sont égales, ce n'est pas une nouvelle entrée, on efface donc la valeur du tableau. Si la valeur du tableau est supérieure, cela signifie que la comparaison se fait avec une valeur d'enregistrement sorti : on incrémente s (nb de sorties et on recompare (dans une boucle interne) ; si égalité avec la nouvelle valeur comparée de l'ancienne base, on efface et on revient à la boucle principale, si inférieure à la nouvelle valeur comparée, c'est qu'il s'agit d'une nouvelle entrée, on incrémente h (nb nouvelles entrées) et retour boucle principale, si toujours supérieure, c'est que c'est encore un enregistrement sorti, on reste dans la boucle secondaire (boucle Do... Loop) pour incrémenter s et recommencer. Si la valeur tableau est inférieure (dans la boucle principale), on incrémente h. On vérifie à chaque tour dans chacune des boucles que l'on n'a pas atteint la dernière ligne de l'ancienne base, auquel cas on arrête les comparaisons.

On a donc en fin de comparaison, un tableau dont chaque élément contenant une valeur (qui n'a pas été effacée) correspond à une nouvelle entrée à extraire, l'indice de l'élément fournissant la ligne sur laquelle se trouve l'enregistrement dans la nouvelle base. Donc à chaque fois que l'on va trouver une valeur on incrémente une variable (pour changer de ligne dans la base des éléments nouveaux) et on y affecte les valeur de la ligne adéquate de la nouvelle base.

La macro est écrite, mais il est sage que je la teste avant de livrer. Pour cela, il faut aménager le modèle pour tester que tout fonctionne dans un cas sans sortie, avec sorties égrenées, et avec sorties consécutives et sorties en début ou fin de base. Compte tenu du décalage horaire, cela attendre demain ! Je n'aurai pas à reprendre les explications données ici.

A+

bonjour,

j'ai pris connaissance de ton souci.

Mais si ça peut te simplifier le problème, je peux te dire que la nouvelle base contient davantage d'enregistrements que l'ancienne base même si il y a quelques désinscrits qui sont sortis du fchier d'une année sur l'autre.

Donc inutile de tester si l'ancienne base dépasse en enregistrements la nouvelle ce n'est jamais le cas !

Le seul dysfonctionnement de la macro antérieure ce sont les quelques désinscriptions qui provoquent une erreur d'execution. (avec le message: indice en dehors de la selection)

Bonjour,

Nouvelle macro (pour remplacer la précédente) :

Sub ExtracNouveaux()
Dim i%, j%, h%, k%, n%, p%, s%, Nv()
[BasAnc].Sort key1:=[NumBA], order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[NumBN], order1:=xlAscending, Header:=xlNo
k = [BasNou].Columns.Count
n = [BasNou].Rows.Count
p = [BasAnc].Rows.Count
ReDim Nv(n)
With [NumBN]
For i = 1 To n
Nv(i) = .Cells(i, 1).Value
Next i
End With
With [NumBA]
For i = 1 To n
If Nv(i) = .Cells(i + s - h, 1).Value Then
Nv(i) = ""
ElseIf Nv(i) > .Cells(i + s - h, 1).Value Then
Do While i + s - h < p
s = s + 1
If Nv(i) = .Cells(i + s - h, 1).Value Then
Nv(i) = ""
Exit Do
ElseIf Nv(i) < .Cells(i + s - h, 1).Value Then
h = h + 1
Exit Do
End If
Loop
Else
h = h + 1
End If
If i + s - h >= p Then Exit For
Next i
End With
h = 0
With [BasNou]
For i = 1 To n
If Nv(i) <> "" Then
h = h + 1
For j = 1 To k
[NvB].Cells(h, j).Value = .Cells(i, j).Value
Next j
End If
Next i
End With
[BasAnc].Sort key1:=[BasAnc].Cells(1, 1), order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[BasNou].Cells(1, 1), order1:=xlAscending, Header:=xlNo
Range([NvB].Cells(1, 1), [NvB].Cells(h, k)).Sort key1:=[NvB].Cells(1, 1), _
order1:=xlAscending, Header:=xlNo
End Sub

En rouge, ce qui diffère de la précédente. Pas mal de choses puisque la méthode utilisée est sensiblement différente.

En espérant avoir testé tous les cas !

bonsoir,

Merci ta macro VBA fonctionne très bien.

Est-ce que tu pourrai la compléter en ajoutant quelques lignes de commentaires ?

Par contre, tu disais que les lignes de code qui effectuent les tris ne sont pas indispensables. Je les ai supprimé carlorsque la macro fait les tris les numéros de clients (une fois triés par ordre croissant) ne correspondent plus aux clients.

Le tri initial pour mettre les numéros de clients en ordre croissant est indispensable, la macro ne fonctionnera pas sans ça.

Le tri final pour les remettre en ordre alpha des noms, lui n'a pas d'utilité pour l'extraction qui à ce moment là est faite.

bonsoir,

La macro à l'air de fonctionner en annulant le tri initial et le tri final mais comme j'ai plus de 3000 enregistrements,. est-ce que le résultat attendu est satisfaisant ? je ne sais pas

Si tu fais un tri initial pour mettre les numéros de clients en ordre croissant, est-ce que les noms de clients restent toujours liés à ces numéros ?

Je voudrai savoir qu'est-ce que je dois modifier dans les lignes de codes de tri car pour traiter ce nouveau fichier, le numéro de client est en colonne 2 et non en colonne 1

j'ai, par exemple modifié dans le reste du code des instructions comme Nv(i) = .Cells(i, 1).Value

en Nv(i) = .Cells(i, 2).Value

ainsi que

If Nv(i) = .Cells(i + s - h, 2).Value Then
Nv(i) = ""
ElseIf Nv(i) > .Cells(i + s - h, 2).Value Then
Do While i + s - h < p
s = s + 1
If Nv(i) = .Cells(i + s - h, 2).Value Then
Nv(i) = ""
Exit Do
ElseIf Nv(i) < .Cells(i + s - h, 2).Value Then

Et bien sur modifié NumBA et NUm BN pour que ces noms correspondent à la colonne 2.

Mais le code des lignes de tri, je ne comprend pas comment ça marche.