Extraire les données d'un tableau vers une colonne sans vide
Bonjour le forum,
Je suis à la recherche d'une solution (VBA ou formule) pour le problème suivant.
J'ai un tableau dont les données (texte) s'étendent de B2:M391. Ce tableau est alimenté par des classeurs tiers et de nombreuses cellules sont vides. Ce que je souhaiterais faire, c'est extraire l'ensemble des données vers la colonne P de la même feuille. Peu m'importe l'ordre, mais il faudrait que les cellules vides n'apparaissent plus.
Solutions déjà testées toutes 2 trouvées sur le site de Jacques Boisgontier (http://boisgontierjacques.free.fr/pages_site/sommeprod.htm#ListeSansVide) :
1) Formule matricielle
=SI(LIGNES($1:1)<=NBVAL($B$2:$M$391);
INDEX($B$2:$M$391;MOD(PETITE.VALEUR(SI($B$2:$M$391<>"";COLONNE($B$2:$M$391)*10^5+LIGNE(INDIRECT("1:"&LIGNES($B$2:$M$391))));LIGNES($1:1));10^5);
ENT(PETITE.VALEUR(SI($B$2:$M$391<>"";COLONNE($B$2:$M$391)*10^5+LIGNE(INDIRECT("1:"&LIGNES($B$2:$M$391))));LIGNES($1:1))/10^5)-COLONNE($B$2:$M$391)+1);"")
Je m'y suis peut-être mal pris, mais la formule me donne une erreur #NOMBRE!. Il faut dire que mon tableau est actuellement complètement vide (il s'agit d'un template). Ca a peut-être une conséquence...
2) Via VBA
- Alt+F11
- Insertion module
- Copier/Coller du code
Dans le tableur
-Sélectionner P2:P391
=ListeSansVides(B2:M391)
-valider avec maj+Ctrl+Entrée
Function ListeSansVides(champ As Range)
Application.Volatile
temp = champ.Value
Dim b()
ReDim b(1 To Application.Caller.Rows.Count)
n = 0
For Each c In temp
If c <> "" Then
n = n + 1
b(n) = c
End If
Next
ListeSansVides = Application.Transpose(b)
End Function
Là, je reçois une erreur #NOM?
A la recherche de vos lumières
Merci à tous !
Bonjour,
cela semble pourtant fonctionner.
joins un fichier avec un exemple de ce que tu veux obtenir.
Bonjour h2so4,
Voilà un fichier exemple de ce qui m'intéresserait. J'avais oublié de préciser que la liste ne peut comporter aucun doublon.
A noter que le tableau (B2:M391) sera alimenté au fur et à mesure par d'autres classeurs via une formule du type =SI(ESTVIDE('[fichier.xlsx]feuil1'!B1);"";'[fichier.xlsx]feuil1'!B1) ce qui explique que certaines cellules seront vides.
J'espère que c'est plus clair. Merci déjà de te pencher sur mon problème.
Bien à toi,
Bonjour,
j'ai adapté la fonction que tu as trouvée sur le net pour y ajouter l'exclusion des doublons.
ajouter la fonction vba via
copier le code ci-dessous
alt-F11 (activer l'éditeur vba)
menu ->insérer ->module
coller le code
alt-F11 (retour dans l'écran excel)
dans l'écran excel
sélectionner une zone suffisamment grande pour contenir tous les projets (exemple O2:O31),
cliquer sur la barre de formule (ce faisant les cellules O2:O31 restent sélectionnées)
introduire la fomule =ListeSansVidesNiDoublons(B2:M391) et valider par CTRL-MaJ-Entrée
j'ai ajouté une option de tri. Pour l'activer il faut modifier la formule
=ListeSansVidesNiDoublons(B2:M391;VRAI) et toujours valider par CTRL-Maj-Entrée
Function ListeSansVidesNiDoublons(champ As Range, Optional sorted As Boolean = False)
Application.Volatile
temp = champ.Value
Dim b() As String
ReDim b(1 To Application.Caller.Rows.Count)
n = 0
For Each c In temp
If c <> "" Then
For i = 1 To n
If b(i) = c Then i = -1: Exit For
Next i
If i > 0 Then
n = n + 1
b(n) = c
End If
End If
Next
'tri
If sorted Then
For i = 1 To n - 1
For j = i + 1 To n
If b(i) > b(j) Then a = b(i): b(i) = b(j): b(j) = a
Next j
Next i
End If
ListeSansVidesNiDoublons = Application.Transpose(b)
End FunctionCa m'a tout l'air de fonctionner à merveille ! En plus de ça, explications très claires et tu rajoutes un tri alphabétique. C'est la cerise sur le gâteau.
Un tout grand merci à toi! Tu me permets de bien avancer.
Bon dimanche !