[RESOLU] Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

[Titou37]

Bonjour à toutes et à tous,

Me revoilà suite à une nouvelle difficulté que je n'arrive pas à contourner. Je m'explique :

Dans un classeur Excel doté de plusieurs feuilles, j'affiche dans la feuille 'Récap' une synthèse d'informations provenant de deux autres feuilles du même classeur ('transfert des données' et 'détail').

Ces données s'affichent grâce à des formules simples avec des Si et des RECHERCHEV.

Mon problème est que mon tableau final ('récap') contient une ligne de données, 1 ligne vide, une nouvelle ligne de données, 3 lignes vides, une ligne de données, 1 ligne vide, une ligne de données, 5 lignes vides, etc.

Bref vous l'aurez compris, le nombre de lignes vides qui s'affichent est variable et je souhaite simplement :

• soit rassembler en haut du tableau toutes les lignes de données en évitant les vides
• soit supprimer les vides, si cela est possible.

Le tout devant fonctionner par formule sans macro ni VBA.

L'objectif est d'améliorer l'affichage sans intervention de l'utilisateur (donc sans utiliser les filtres, les tris ou autres).

Je ne sais pas si c'est réalisable mais je n'y arrive pas.

Je vous remercie d'avance de l'aide que vous pourrez m'apporter.

Et comme un exemple vaut mieux qu'un long discours, je vous joins mon classeur exemple.

Cordialement.
Titou37

 

[Staple1600]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour à tous


Titou37
As-tu essayer d'appliquer un tri sur tes données?
ou un filtre pour masquer les lignes vides ?

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour à tous


Titou37
As-tu essayer d'appliquer un tri sur tes données?
ou un filtre pour masquer les lignes vides ?

Bonjour Staple1600,

Merci de ton aide, mais c'est justement ce que je souhaite éviter, l'utilisateur final du tableau doit pouvoir le lire sans avoir à y intervenir (sans filtre, ni tri).
Il me semble que l'on peut y parvenir (INDEX, EQUIV, DECALER ?) mais je n'y arrive pas.

Cordialement.
Titou37

 

[Modeste]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour Titou37,
Salut JM

En B13 de la feuille "Récap", tu peux utiliser une formule matricielle:

=SIERREUR(INDEX('Transfert des données'!$A$1:$A$60;PETITE.VALEUR(SI('Transfert des données'!$AB$7:$AB$60="CPAM de A";LIGNE('Transfert des données'!$AB$7:$AB$60));LIGNES($1:1)));"")

Valider avec Ctrl+Shift+Enter, puis recopier en bas

pour les autres colonnes, il me semble que j'utiliserais une RECHERCHEV (combinée avec un format personnalisé, pour l'affichage des espaces en colonne C)

N'hésite pas à nous dire si tu as des questions!

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour Modeste et merci pour ta proposition super rapide !
Je vais tester ça cet après-midi et je te tiens informé des résultats.
Merci.
Titou37

 

[Modeste geedee]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonsour®
2 propositions :

1ere
Avec tri préalable de la table Transfert de données sur la colonne AB(Nom Centre SS)
--- + modification N° SS : saisie sans espace , application Format personnalisé :
[>=3000000000000]#" "##" "##" "##" "###" "###" "##;#" "##" "##" "##" "###" "###
--- + modification des formules feuille Recap

2éme
via Tableau croisé dynamique
--- + ajout colonne supplémentaire pour cumul Séances (somme colonne K à Z)



Nb : éviter les cellules fusionnées, lignes et colonnes vides au sein des données
séparer les calculs de la présentation

les modification proposées sur le données transférées, peuvent être effectuées automatiquement si utilisation de transfert par macro

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour Titou37,
Salut JM

En B13 de la feuille "Récap", tu peux utiliser une formule matricielle:

=SIERREUR(INDEX('Transfert des données'!$A$1:$A$60;PETITE.VALEUR(SI('Transfert des données'!$AB$7:$AB$60="CPAM de A";LIGNE('Transfert des données'!$AB$7:$AB$60));LIGNES($1:1)));"")

Valider avec Ctrl+Shift+Enter, puis recopier en bas

pour les autres colonnes, il me semble que j'utiliserais une RECHERCHEV (combinée avec un format personnalisé, pour l'affichage des espaces en colonne C)

N'hésite pas à nous dire si tu as des questions!

Ta formule matricielle est extra!
Pour ce qui est de la colonne C, j'ai voulu suivre tes conseils et remplacer mon usine à gaz par une simple recherchev. Pas de problème pour la recherche ça fonctionne bien, ça je maîtrise par contre je n'ai jamais réussi à lui faire appliquer un format type n°SS en utilisant :
'=SI(ET('Transfert des données'!$AB7="CPAM de A";'Transfert des données'!G7<>0);TEXTE(RECHERCHEV(B13;transfert_donnees;7;FAUX);"# ## ## ## ### ### ##");"").

J'ai tenté diverses combinaisons de formats en m'inspirant du format n°SS Excel : [>=3000000000000]#" "##" "##" "##" "###" "###" | "##;#" "##" "##" "##" "###" "###. Mais là encore, cruelle déception, je n'y arrive pas.

Le n° SS arrive dans la feuille transfert avec un espace devant la clé, je pense que c'est là que ce situe le pb, j'ai donc essayé d'enlever l'espace par formule mais encore une fois je n'y arrive pas.

Je te joins mon essai à tout hasard.

Cordialement.
Titou37

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonjour Modeste geedee et merci pour ta proposition intéressante.

Effectivement je ne m'étais pas penché sur l'éventualité d'un tableau croisé dynamique, c'est une piste intéressante que je vais potasser.

Mon problème est que je n'ai pas "la main" sur la feuille de transfert des données car elle m'arrive en l'état et l'utilisateur final veut produire son tableau récap chaque sans avoir à toucher à la feuille de transfert, ni faire appel à moi pour la retoucher. Il faut que je trouve une astuce pour que le formatage du n° de SS soit correct.
J'ai essayé d'inclure la fonction TEXTE() pour formater le n° dans le récap mais je n'y arrive pas, comme tu le verras dans le classeur Excel que j'ai joins à Modeste dans ma dernière réponse.

Je te remercie de ton aide.

Cordialement.
Titou37

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Je crois que je suis sur la bonne voie, je suis en train de tester la formule suivante pour formater le n° de SS à l'arrivée dans le récap :
=SI(ET($B13<>"";NBCAR(SUBSTITUE(RECHERCHEV($B13;transfert_donnees;7;FAUX);" "; ""))>=13);CNUM(SUBSTITUE(RECHERCHEV($B13;transfert_donnees;7;FAUX);" "; ""));"")

Mes premiers essais semblent concluant après nettoyage des espaces dans les n° transférés.

Je vous tiens informés.

Merci à tous.

Cordialement.
Titou37

 

[Titou37]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

J'ai finalement légèrement modifié la recherche du n° de SS pour tenir compte des cas sans n° qui généraient des erreurs de type #N/A :

=SI(ESTERREUR(RECHERCHEV($B14;transfert_donnees;7;FAUX))=VRAI;"";SI(ET($B14<>"";NBCAR(SUBSTITUE(RECHERCHEV($B14;transfert_donnees;7;FAUX);" ";""))>=13);CNUM(SUBSTITUE(RECHERCHEV($B14;transfert_donnees;7;FAUX);" ";""));""))

J'attends la fin de mes tests avant de clore la discussion.

Merci à tous.

Cordialement.
Titou37

 

[Modeste geedee]

Re : Eviter (ou supprimer) les lignes vides générées par la recopie d'une formule

Bonsour®

Mon problème est que je n'ai pas "la main" sur la feuille de transfert des données car elle m'arrive en l'état et l'utilisateur final veut produire son tableau récap chaque sans avoir à toucher à la feuille de transfert, ni faire appel à moi pour la retoucher.

GIGO — Wiktionnaire