Concaténer des données de plusieurs colonnes sur une cellule
Bonjour,
je dois créé un fichier csv comprenant une blacklist d'adresses et plages IP. Voici le matériau d'origine sous Excel 2016 français:
Sur les 4 premiers onglets, je saisis les "adresses" et "CIDR" (quand il y en a). À partir de ça, sur chaque onglet, j'ai créé une colonne qui concatène les éléments afin d'obtenir la forme souhaitée dans le csv: host,nom,adresse,,static,,"" ou network,nom,adresse,masque,CIDR,,,"" et je les ai reporté sur le 5ème onglet "SUM".
Jusque là, rien de bien méchant.
Dans un premier temps, je souhaiterais trouver une formule qui permet d'empiler le contenu de ces colonnes en une seule, la 2ème à la suite de la 1ère, la 3ème à la suite de la 2ème, etc... comme affiché dans la colonne CSV. Il y a bien le "copier/coller", mais si on peut automatiser le procédé, c'est mieux.
Dans un second temps, et c'est là tout l'objet de ma requête finalement, je voudrais que, dans la dernière cellule de cette colonne, je puisse concaténer tous les contenus des colonnes "noms" des quatre 1er onglets. group,BLACKLIST,"black_addresse_1,black_adresse_2,etc, black_plage_1,black_plage_2,etc , black_tor_1,black_tor_2,etc, black_plage_tor_1,black_plage_tor_2,etc",""
J'ai bien essayé avec le combo
- = CONCATENER (Ax: Ay & ","),etc
- F9 sur la partie Ax: Ay & ","
- suppression des {}
mais c'est assez laborieux comme manip et on est limité en caractères.
Au pire, si on est obligé de faire une cellule par onglet, je suis preneur aussi.
J'espère avoir été assez clair, D'avance merci pour vos retours.
Bonjour,
Vous devriez regarder comment fonctionne l'outil PowerQuery dans Excel. Sur la version 2016 il est impossible de faire ce que vous souhaitez via des formules. c'est à la rigueur possible via VBA mais PQ est beaucoup plus adapté.
Dites moi laquelle des solutions vous pourriez envisager.
Alternativement un petit script python doit aussi etre possible.
Bonjour,
Je ne connais pas du tout PowerQuery mais je peux regarder en effet. Après si c'est possible en macro, c'est intéressant également.
Donc, dans un premier temps, on va commencer avec Powerquery. on verra après.
Encore merci.
Re,
Je ne suis pas un expert en PQ, il faudrait l'avis affuté de JFL, mais je vous joins ci-après un essai que j'ai pu faire.
Entrez en B1 de la feuille "input" le chemin d'accès à votre classeur blacklist.xlsx, puis rafraichissez la Query (onglet Données / montrer les queries si non affiché).
J'ai pu modifier le chemin d'accès mais quand je mets à jour, il me met une erreur. Et quand j'arrive à ouvrir l'éditeur Power Query mais page blanche et menus inopérants. Quand je veux créer une nouvelle requête j'ai encore une erreur.
Ma version d'Excel est bridée par mon entreprise et mon PC n'en est pas à sa première jeunesse.
Je continue à regarder mais je crois que l'option Power Query s'eloigne.
Ci-joint votre fichier avec une petite macro VBA dont le code se trouve ci-dessous :
Sub GroupColumns()
Dim colsToAdd As Range
With ThisWorkbook.Worksheets("SUM")
.Activate
On Error Resume Next
Set colsToAdd = Application.InputBox("Sélectionnez les colonnes à empiler", "Entrée des valeurs", "A:D", Type:=8)
If colsToAdd Is Nothing Then Exit Sub
On Error GoTo 0
End With
' suppression cellules vides et sauvegarde dans une array
Dim datas
datas = colsToAdd.SpecialCells(xlCellTypeFormulas).Value2
' sauvegarde dans une arraylist
Dim datas1Col As Object
Set datas1Col = CreateObject("System.Collections.ArrayList")
Dim i As Long, j As Long
For i = LBound(datas, 1) To UBound(datas, 1)
For j = LBound(datas, 2) To UBound(datas, 2)
If Not CStr(datas(i, j)) = vbNullString Then
datas1Col.Add CStr(datas(i, j))
End If
Next j
Next i
datas1Col.Sort
'export vers excel de la arraylist
Dim exportTopLeft As Range
Set exportTopLeft = ThisWorkbook.Worksheets("CSV").Range("A1") ' cellule adaptable au besoin
exportTopLeft.Columns.ClearContents
exportTopLeft.Resize(datas1Col.Count, 1).Value2 = Application.Transpose(datas1Col.ToArray)
exportTopLeft.Columns.AutoFit
End SubÇa marche au poil, un grand merci.
Vais abandonner le 2ème point de ma demande (cellule avec que les "noms"), après étude un peu plus approfondie, il s'avère que je peux me passer de cette fonction donc vais rester sur mon empilement de colonnes.
Un grand merci Saboh, vous m'avez bien dépatouiller.
Je clôture le post.
Très bien,
Désolé j'avais oublié cette histoire de noms.
Ci-après la macro corrigée pour renvoyer les noms en colonne B. En supposant que le nom est toujours le 2e paramètre dans la liste (découpe entre les ",").
Bonne journée
Sub GroupColumns()
Dim colsToAdd As Range
With ThisWorkbook.Worksheets("SUM")
.Activate
On Error Resume Next
Set colsToAdd = Application.InputBox("Sélectionnez les colonnes à empiler", "Entrée des valeurs", "A:D", Type:=8)
If colsToAdd Is Nothing Then Exit Sub
On Error GoTo 0
End With
' suppression cellules vides et sauvegarde dans une array
Dim datas
datas = colsToAdd.SpecialCells(xlCellTypeFormulas).Value2
' sauvegarde dans une arraylist
Dim datas1Col As Object, noms As Object
Set datas1Col = CreateObject("System.Collections.ArrayList")
Set noms = CreateObject("System.Collections.ArrayList")
Dim i As Long, j As Long, concat As String
For i = LBound(datas, 1) To UBound(datas, 1)
For j = LBound(datas, 2) To UBound(datas, 2)
concat = CStr(datas(i, j))
If Not concat = vbNullString Then
datas1Col.Add concat
noms.Add CStr(VBA.Split(concat, ",")(1))
End If
Next j
Next i
datas1Col.Sort
noms.Sort
'export vers excel de la arraylist
Dim exportTopLeft As Range
Set exportTopLeft = ThisWorkbook.Worksheets("CSV").Range("A1") ' cellule adaptable au besoin
exportTopLeft.Columns.ClearContents
With exportTopLeft.Resize(datas1Col.Count, 1)
.Value2 = Application.Transpose(datas1Col.ToArray)
.Offset(0, 1).Value2 = Application.Transpose(noms.ToArray)
.Columns.AutoFit
.Offset(0, 1).Columns.AutoFit
End With
ThisWorkbook.Worksheets("CSV").Activate
End SubPas de soucis, je regarderais ça quand même.
Encore merci.