Extraire des données d'une liste selon un critère Fermé

Question

Bonjour, 

j'ai un problème de formule. 

Dans un onglet "MV Daten" j'ai sur la colonne B des numéros d'immeubles. 
Dans la colonne D à coté j'ai les noms des locataires (un locataire peut avoir plusieurs appartements dans le même immeuble, il est donc important de garder les doublons). 

Je vais donc avoir plusieurs lignes pour l'immeuble 38 (donc dans la colonne B, le nombre 38 va apparaitre une vingtaine de fois, car j'ai 20 appartements dans cet immeuble - pas forcement des locataires différents). 
J'ai créer plusieurs onglets pour chaque immeuble et la cellule D3 dans l'onglet des immeubles (donc pas "MV Daten") nous donne le numéro de l'immeuble et je souhaiterais avec une formule (sans macro) extraire dans l'onglet "immeuble 38" (par ex.) le nom de tous les locataires en concervant l'ordre et les doublons. 

Il faut savoir que dans l'onglet "MV Daten" j'ai environ 2500 lignes (locataires) et que pour l'immeuble 38 j'en ai que 20. Par conséquence la fonction SI ne me convient pas car mon onglet "immeuble 38" ne doit pas contenir 2500 lignes. RechercheV ne prend pas en compte les doublons et prend une valeur unique. 

Est-ce que quelqu'un peut m'aider?
Merci beaucoup 
Louise


Configuration: Windows XP / Internet Explorer 8.0

Babar64420 · Answer

Bonjour,

"Sans macro" cela me parait infaisable, ou alors extrêmement lourd (formules "fractionnées" dans plusieurs cellules).

Pourquoi excluez-vous les macros ?

louise · Answer

Bonjour Babar,
tout d'abord merci d'avoir pris le temps de lire et de répondre. 

Je ne souhaite pas utiliser de macro, car j'ai un onglet modèle, et ensuite à partir de cet onglet modèle j'ai une macro qui génère tous les onglets de tous les immeubles. Chaque onglet est nommé avec le numéro de l'immeuble (environ 100 immeubles) et dans la cellule D3, le nom de l'onglet est recopier. J'ai donc environ 100 onglets qui sont une copie de mon onglet modèle (la ou je veux mettre mes formules) - la seule différence est le numéro de l'immeuble (en cellule D3). Avec la formule les locataires seraient actualisés avec un F9 pour chaque onglet. 
Voilà pourquoi je voudrais avoir que des formules dans l'onglet modèle. 
Louise

Babar64420 · Answer

Louise,
Je comprends mal ce que vous voulez faire au bout du compte : "extraire dans l'onglet "immeuble 38" (par ex.) le nom de tous les locataires en concervant l'ordre et les doublons."
D'où se fait cette "extraction"?
La FEUILLE "immeuble 38" contient quoi?
Comment entrez-vous vos données, par la FEUILLE "Modèle"?
Sur quelle FEUILLE se trouve cette cellule "D3"?
Merci de me détailler le déroulement de la séquence d votre procédure.

g · Answer

Bonjour,

Feuille MV Daten tu nommes le colonne B ref et la colonne D val
Sur les feuilles immeuble, dans la cellule de ton choix tu copies la formule matricielle à dérouler vers le bas après validation

{=INDEX(val;PETITE.VALEUR(SI(ref=$D$3;LIGNE(INDIRECT("1:"&LIGNES(ref))));LIGNES($1:1)))}

Pour valider une fonction matricielle utiliser les touches Ctrl+Maj+Entrée, ce qui ajoutera les crochets {} qui ne doivent pas être ajoutés manuellement.

et si tu veux éviter les #NOMBRE! dans les cellules où il n'y a pas de réponse valide tu copies la formule matricielle à dérouler vers le bas après validation

{=SI(ESTERREUR(INDEX(val;PETITE.VALEUR(SI(ref=$D$3;LIGNE(INDIRECT("1:"&LIGNES(ref))));LIGNES($1:1))));"";INDEX(val;PETITE.VALEUR(SI(ref=$D$3;LIGNE(INDIRECT("1:"&LIGNES(ref))));LIGNES($1:1))))} 

Cordialement.

louise · Answer

Merci à tous pour vos réponses. 
Ca marche très bien, le seul problème c'est que lorsque j'ai généré un onglet par immeuble sur la base de l'onglet modèle et que je veux actualiser tous ces onglets (environ 100) ca met un temps fou. Je vais donc essayer de faire finalement une macro pour régler le problème. Si vous avez une idée je suis preneuse, je m'y mets de mon coté! 
Merci encore. 
Louise

louise · Answer

bon alors je me suis collée à la macro et j'ai un probleme. QQun peut-il m'aider?
Pour donner une idée dans MV Daten j'ai plusieurs colonnes:
Colonne B = Numéro d'immeuble
Colonne C = Propriétaires
Colonne D = locataires
...jusque K
J'ai créer des onglets pour chaque immeuble et l'idée c'est de recopier toutes les données de B à K qui correspondent au numéro de l'onglet (donc de l'immeuble).

Les cellules n1 et o2 sont vides, elles sont juste utilisées par la macro pour définir les critères (numéros d'immeuble) au fur et à mesure et en fonction de la formule définie en "Liste" TY (cf ci dessous).
Mon bug est dans la phrase suivante:
           .Range("a1:k" & Lg").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
           .Range("o1:o2"), CopyToRange:=Sheets(Cel).Range("b9:k9"), Unique:=False


Voici la macro:
     
 
Sub Extractions3()
Dim Lg%, Cel As Range, x
           Application.ScreenUpdating = False
        
    With Sheets("MV Daten")
        'Lg = .Range("a65536").End(xlUp).Row + 1
        For Each Cel In Range("TY")                
           .Range("n1") = Cel                      
           .Range("o2") = "=b5=$n$1"               
           .Range("a1:k" & Lg").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
           .Range("o1:o2"), CopyToRange:=Sheets(Cel).Range("b9:k9"), Unique:=False
        Next Cel
            .Range("n1:o2").ClearContents
    End With
        
End Sub

sachant que TY est la plage nommé dans un onglet "Liste" avec tous mes numéros d'immeubles avec une formule "décaler"
TY=Decaler(Liste!$A$2;;;NBVAL(Liste!$A:$A)-1)

Merci d'avance pour votre aide!
Louise

Extraire des données d'une liste selon un critère

6 réponses

Discussions similaires