XL 2019 Recopier Liste avec une valeur en Moins

sylvanu

XLDnaute Barbatruc

Supporter XLD

• Lundi à 16:58

• #2

Bonsoir Caninge,
Un essai en PJ avec une petite macro.
et une V2 pour le fun, en utilisant qu'un seul array.

 

Pièces jointes

• recopier liste Colonne en otant une valeur.xlsm

  24.3 KB · Affichages: 8
• recopier liste Colonne en otant une valeur V2.xlsm

  24.2 KB · Affichages: 6

Dernière édition: Lundi à 17:24

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• Lundi à 17:20

• #3

Bonjour Caninge, sylvanu,

Depuis le temps qu'on manipule des formules matricielles vous devriez faire ça tout seul, en B4 :

VB:

=SIERREUR(INDEX('Liste joueurs'!B:B;PETITE.VALEUR(SI(('Liste joueurs'!B$3:B$1000<>"")*('Liste joueurs'!B$3:B$1000<>B$2);LIGNE('Liste joueurs'!B$3:B$1000));LIGNE(B1)));"")

A+

 

Pièces jointes

• recopier liste Colonne en otant une valeur.xlsx

  13.1 KB · Affichages: 6

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• Lundi à 18:14

• #4

Bonjour Job75, Sylvanu

Oui effectivement depuis le temps. ce n'est pas facile pour moi. J'aimerais bien !!! je me défends peut-être mieux au scrabble

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• Lundi à 18:54

• #5

Une solution VBA avec cette macro dans le code de la feuille "Résultats" :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.ScreenUpdating = False
With Target(3).Resize(100) '100 à adapter
    Sheets("Liste joueurs").[B3].Resize(100).Copy .Cells
    .Replace Target, "", xlWhole
    On Error Resume Next 'si aucune SpecialCell
    .SpecialCells(xlCellTypeBlanks).Delete xlUp
End With
End Sub

Elle se déclenche quand on modifie la cellule B2.

Edit : on peut ne copier que les valeurs, c'est plus rapide :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.ScreenUpdating = False
With Target(3).Resize(100) '100 à adapter
    .Value = Sheets("Liste joueurs").[B3].Resize(100).Value
    .Replace Target, "", xlWhole
    On Error Resume Next 'si aucune SpecialCell
    .SpecialCells(xlCellTypeBlanks).Delete xlUp
End With
End Sub

 

Pièces jointes

• recopier liste Colonne en otant une valeur.xlsm

  19.9 KB · Affichages: 3

Dernière édition: Lundi à 20:00

Vote positif 0 Vote négatif

P

Phébus

XLDnaute Nouveau

• Lundi à 20:01

• #6

Bonjour à tous

Tu peux utiliser cette formule pour avoir la liste filtrée (sans la valeur de "B2").

=FILTRE('Liste joueurs'!B3:B10;'Liste joueurs'!B3:B10<>Résultats!B2)

mettre une liste déroulante en B2

Cordialement

 

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• Mardi à 09:57

• #7

Bonjour Phébus, Job75, sylvanu
je vois que vous avez chacun votre technique. VBA, formules et filtre.
Il faut choisir.
CANINGE

 

Vote positif 0 Vote négatif

O

OKBI

XLDnaute Occasionnel

• Mardi à 12:59

• #8

Bonjour à tous,

Un essai avec Power Query
Cdt

 

Pièces jointes

• recopier liste Colonne en otant une valeur v2.xlsm

  30.1 KB · Affichages: 2

Vote positif 0 Vote négatif

AtTheOne

XLDnaute Accro

Supporter XLD

• Mardi à 18:28

• #9

Bonsoir à toutes & à tous, bonsoir @Caninge

Avec la solution par formule de @job75, en utilisant des tableaux structurés et des noms définis (pour éviter la validation par CTRL MAJ ENTRER).

Les nom définis :

Noms définis
Diff_Nb_Lignes	=(LIGNES(ts_Extrait[Nom])+N(Joueur_Exclu<>"")-LIGNES(ts_Joueurs[Joueur]))
Joueur_Exclu	=Résultats!$B$2
Joueur_Extrait	=SIERREUR(INDEX(ts_Joueurs;PETITE.VALEUR(SI((ts_Joueurs<>Joueur_Exclu);LIGNE(ts_Joueurs)-LIGNE(ts_Joueurs[#En-têtes]));LIGNE()-LIGNE(ts_Extrait[#En-têtes])));"")

Données des tableaux structurés
ts_Joueurs	'Liste joueurs'!$B$3:$B$10
ts_Extrait	Résultats!$B$7:$B$14

deux petites démos :





Voir le fichier joint
À bientôt

 

Pièces jointes

• recopier liste Colonne en otant une valeur AtTheOne.xlsx

  14.1 KB · Affichages: 4

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• Mardi à 19:12

• #10

Bonsoir AtTheOne,

L'utilisation de tableaux structurés est tout à fait utile.

Mais éviter la validation matricielle ne diminue pas la durée du recalcul.

Tes formules comme les miennes (avec bien sûr la plage 'Liste joueurs'!B$3:B$10) se recalculent chez moi en 0,23 ms.

A+

 

Vote positif 0 Vote négatif

AtTheOne

XLDnaute Accro

Supporter XLD

• Mardi à 19:38

• #11

Salut @job7,

Oui, je n'ai pas essayé d'accélérer les calculs, juste utiliser les tableaux structurés, et éviter la validation matricielle (puisque les formules rentrées dans des noms définis sont d'emblée matricielles).
Et puis, question de goût, je trouve les formules avec des noms definis saisies dans la feuille plus parlantes (à condition que les noms eux-même soient parlants) ...
Bref c'était une proposition sans aucune prétention et une occasion de faire un post comme j'arrive toujours un peu tard.

À bientôt

 

Vote positif 0 Vote négatif

Caninge

XLDnaute Accro

• Hier à 10:29

• #12

bonjour à tous,
merci pour toutes vos propositions !
je regarde tout ça !
a bientôt
CANINGE

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• Hier à 17:05

• #13

Bonjour le forum,

Dans les fichiers joints on calcule les durées d'exécution.

1) VBA.xlsm : même avec 100 000 lignes c'est très rapide.

Curieusement ma macro est plus rapide quand on copie tout que quand on copie les valeurs.

Je n'ai pas d'explication à cela, si vous en avez une merci de me la donner.

2) Formules.xlsm : avec 10 000 lignes il faut environ 20 secondes chez moi.

Les formules sont rendues volatiles par T(ALEA()) et sont recalculées avec Calculate.

A+

 

Pièces jointes

• VBA.zip

  548.1 KB · Affichages: 1
• Formules.xlsm

  426.9 KB · Affichages: 0

Vote positif 0 Vote négatif