Croiser lignes et colonnes SANS TCD

**Jaymerry** · 31/12/2010, 15h58

Hello à tous,

Je suis face à une colle ; je dispose sur une feuille Excel des correspondances entre deux colonnes (A et B).

Je souhaiterais traiter en VBA cette correspondance de manière à afficher sur une nouvelle feuille la colonne A en colonne, et la colonne B transposée en-têtes mais en ligne. Les correspondances entre ces colonnes apparaitraient sous forme de "x" au croisement de chacune d'elles. En somme ce schéma :

Feuille 1 :
Col A et Col B

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Feuille 2 :
==>

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Niveau transposition, c'est tout bon, j'ai la mise en forme attendue avec des range. Ce que j'essaie de trouver, c'est le type de boucle nécessaire qui parcourrait les lignes de feuille 2 pour aller chercher les références entre lignes / colonnes présentes dans la feuille 1

Je sais qu'un TCD permet justement ce type de mécanisme, malheureusement une des contraintes est justement de s'en passer...

Merci à tous pour votre aide et bonne fin d'année !

Jaymerry

**cb_60** · 31/12/2010, 17h39

Bonjour
En attendant que l'on te le propose en VBA

Deux zones nommées

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
colA =DECALER(Feuil1!$A$2;;;NBVAL(Feuil1!$A:$A)-1)
colB =DECALER(colA;;1)

en colonne A de la feuille 2 la formule à mettre en A2 et tirer vers le bas
MATRICIEL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTNA(INDEX(colA;EQUIV(0;NB.SI($A$1:A1;colA);0)));"";INDEX(colA;EQUIV(0;NB.SI($A$1:A1;colA);0)))

en ligne B1 de la feuille 2 la formule à tirer sur la droite
MATRICIEL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTNA(INDEX(colB;EQUIV(0;NB.SI($A$1:A1;colB);0)));"";INDEX(colB;EQUIV(0;NB.SI($A$1:A1;colB);0)))

et dans le tableau en B2, à tirer sur le droite et le bas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(SOMMEPROD((colA=$A2)*(colB=B$1))<>0;"X";"")

**Jaymerry** · 04/01/2011, 09h58

Bonjour et merci beaucoup pour cette piste (et bonne année à tous !)

Je vais regarder ça de plis près cette piste. Le plus compliqué coté va être de créer une boucle optimisée niveau perf....

Merci à tous pour votr aide

**Philippe Tulliez** · 04/01/2011, 12h14

Bonjour,
Voici une solution en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub Transpose()
 Dim CellFrom As Range, CellTo As Range
 Dim r As Single, FindPosition As Single, cCel As Single
 Dim FindTable As Range
 cCel = 1 ' Compteur
 With ThisWorkbook
  Set CellFrom = .Worksheets("Feuil1").Range("A2")
  Set CellTo = .Worksheets("Feuil2").Range("B2")
 End With
 For r = 0 To CellFrom.End(xlDown).Row - 2
  With CellTo
  .Offset(r + 1, 0) = CellFrom.Offset(r, 0)
   Set FindTable = Range(Cells(.Row, .Column + 1), Cells(.Row, .Column + 1 + cCel))
   On Error GoTo ErrorHandler
   FindPosition = Application.WorksheetFunction.Match(CellFrom.Offset(r, 1), FindTable, False)
   If FindPosition = 0 Then FindPosition = cCel: cCel = cCel + 1
   On Error GoTo 0
   .Offset(.Row - 2, FindPosition) = CellFrom.Offset(r, 1)
  .Offset(r + 1, FindPosition) = "x"
  End With
 Next
 Exit Sub
ErrorHandler:
 If Err.Number = 1004 Then FindPosition = 0: Resume Next Else MsgBox "Problème"
End Sub

**Jaymerry** · 10/01/2011, 02h03

Bonsoir,

Merci beaucoup pour cet exemple de code.
Cependant, je dispose déjà des "en-têtes" (en colonnes et en lignes) sur la feuille 2. Je cherche désormais à alimenter ce tableau sans modifier l'ordre de ces en-têtes, en cochant les cellules correspondant au croisement de ces en-têtes (correspondances présentes dans la feuille 1).

Disposant d'un certain nombre de lignes (6000), la difficulté est pour moi de trouver code optimisé et cohérent (via ADO ??).

Merci à tous pour votre aide,

Jaymerry

**Philippe Tulliez** · 10/01/2011, 07h51

Bonjour,
Je n'ai pas le temps de revoir mon code ce matin, mais il me semble qu'il doit fonctionner même si les en-têtes existent déjà. Dans le cas contraire la modification doit être mineure.
Si personne n'a répondu d'ici ce soir, je renverrai un message après vérification.

**Jaymerry** · 10/01/2011, 13h10

Bonjour,

En fait la principale difficulté est surtout de conserver l'ordre de ces en-têtes, de manière à ce que les "x" ne soient pas affichés uniquement en diagonale.

Merci à tous pour votre aide,

Jaymerry

**Philippe Tulliez** · 10/01/2011, 21h22

Bonsoir,
A la relecture de ton 'post', je crois que je ne comprends plus très bien ta demande.

Envoyé par Jaymerry

Cependant, je dispose déjà des "en-têtes" (en colonnes et en lignes) sur la feuille 2. Je cherche désormais à alimenter ce tableau sans modifier l'ordre de ces en-têtes, en cochant les cellules correspondant au croisement de ces en-têtes (correspondances présentes dans la feuille 1).

Si comme tu le dis, tu as déjà les lignes et colonnes en place sur la feuille 2, Tu n'as sans doute absolument pas besoins de VBA.
La fonction SOMMEPROD doit vraisemblablement répondre à tes besoins.
Pour ma part, une nouvelle explication ne serait pas superflue

**Jaymerry** · 11/01/2011, 14h47

Bonjour,

Effectivement tu as raison, l'utilisation de la fonction SOMMEPROD me permet d'obtenir les correspondances des colonnes de la feuille 1 sur la feuille 2. Je cherche maintenant à voir comment optimiser l'utilisation de cette fonction en VBA, voir de trouver une solution alternative toujours en VBA.

Comme je le disais, j'ai une volumétrie assez conséquente (6000 lignes x 100 colonnes) pour ce type de traitement, et l'utilisation manuelle de cette fonction (comme proposée précedemment) est assez gourmande. Peut être qu'une fois programmée en VBA, les temps seront réduits, je vais voir.

J'ai cherché du côté des connexions ADO que je trouve assez bluffantes en terme de performances, mais n'ait pas réussi à développer quelque chose pouvant répondre à mon besoin (exprimer les correspondances de deux colonnes dans une matrice booléenne).

Merci à tous pour votre aide ,

Jaymerry

**mercatog** · 11/01/2011, 16h19

Feuil1, nomme la plage de données de la colonne A Cod et la plage de données de la colonne B Vale
applique ce code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
With Sheets("Feuil2").Range("B2:D20") 'à adapter
   .FormulaR1C1 = "=if(SumProduct((Cod=RC1)*(Vale=R1C)*1)>0,""X"","""")"
   .Value = .Value
End With

**Jaymerry** · 14/01/2011, 19h04

Merci pour ce code.

Mais je m'arrache un peu les cheveux : comment faire maintenant pour "décoller" la matrice de résultat de la feuil2 des en-têtes ? En somme comment indiquer au code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 .FormulaR1C1 = "=if(SumProduct((Cod=RC1)*(Vale=R1C)*1)>0,""X"","""")"

que les en-têtes colonnes / lignes ne sont pas directement collés à la matrice. Par exemple, titres des lignes de la matrice en B2, et titre des colonnes en A3. La matrice de résultat, se trouverait en (B4:E15) par exemple.

Merci pour votre aide

**Qwazerty** · 14/01/2011, 19h36

Salut
Voila pour placer les X au bon endroit.
Une chose me dérange par contre, si je déclare NStrLigne en String, VLookUp me retourne bien un string, donc pas de soucis, par contre Match ne retrouve pas la valeur string NStrLigne ... bizarre.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub Macro()
Dim TheCell As Range
Dim NStrLigne As Integer, NCol As Integer
Dim DBFeuil1
 
'On memorise la plage de donnée de la feuil1
Set DBFeuil1 = Feuil1.Range("A2", Feuil1.Cells(Rows.Count, "B").End(xlUp))
 
 
'On boucle sur le contenu de la 1er colonne feuil2
For Each TheCell In Feuil2.Range("A2", Feuil2.Cells(Rows.Count, "A").End(xlUp))
    'On cherche ensuite la correspondance avec le tableau feuil1 colonne A et on regarde la valeur correspondante en colonne B
    NStrLigne = WorksheetFunction.VLookup(TheCell.Value, DBFeuil1, 2, True)
    'On recherche ensuite ce numero dans la l'entete de ligne feuil2 et on retourne l'emplacement de la colonne
    NCol = WorksheetFunction.Match(NStrLigne, Feuil2.Range("B1", Feuil2.Cells(1, Columns.Count).End(xlToLeft)), 0)
 
    'On place ensuite la coche au bon aendroit
    TheCell.Offset(0, NCol).Value = "X"
Next
 
End Sub

++
Qwaz

**mercatog** · 14/01/2011, 20h34

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
With Sheets("Feuil2").Range("B4:E15") 'à adapter
   .FormulaR1C1 = "=if(SumProduct((Cod=R[-1]C1)*(Vale=R2C)*1)>0,""X"","""")"
   .Value = .Value
End With

L'astuce est de chercher le décalage relatif par rapport à B4 de la position des cellules nom de colonne et nom de ligne correspondants à B4 (ici A3: R[-1]C1 et B2: R2C)
avec Cod nom de plage de données de la colonne A de feuil1
et Vale nom de plage de données de la colonne B de feuil1

**Jaymerry** · 18/01/2011, 11h51

Merci pour ces réponses.
Le Vlookup est une alternative intéressante à l'utilisation du SUMPRODUCT (qui est très consommateur en terme de ressources - merci au passage pour les infos sur l'utilisation de FormulaR1C1, beaucoup plus claire pour moi).

Cependant, dans certains cas, un seul Vlookup ne permet pas de générer toutes correspondances :

Feuille 1 :
colA et colB

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Feuille 2 :
==>

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

En somme, si une 2e entrée est présente sur une ligne de la feuille 2, la boucle ne marquera pas la cellule (le "x" représentant la correspondance de X4 n'est pas généré). Je suppose que pour réaliser cela, il faudrait imbriquer une seconde boucle dans le code, afin de parcourir pour chaque ligne, les colonnes de la feuille 1. Problème : deux "for each" imbriqués ne fonctionnent pas...

Merci pour votre aide

**Qwazerty** · 18/01/2011, 13h40

Salut
Si j'ai bien compris, ton exemple est faux, puisque la dernière ligne ayant X comme entête, la case validé devrait être sur la 1ère ligne en colonne 3.

Essai comme ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Macro()
Dim TheCell As Range
Dim NLigne As Integer, NCol As Integer
Dim DBFeuil1
 
'On memorise la plage de donnée de la feuil1
Set DBFeuil1 = Feuil1.Range("A2", Feuil1.Cells(Rows.Count, "B").End(xlUp))
 
 
'On boucle sur le contenu de la 1er colonne feuil2
For Each TheCell In DBFeuil
    NCol = WorksheetFunction.Match(TheCell.Offset(0, 1).Value, Feuil2.Range("B1", Feuil2.Cells(1, Columns.Count).End(xlToLeft)), 0)
    NLigne = WorksheetFunction.Match(TheCell.Value, Feuil2.Range("A1", Feuil2.Cells(Rows.Count, "A").End(xlUp)), 0)
    'On place ensuite la coche au bon aendroit
    Feuil2.Range("A1").Offset(NLigne, NCol).Value = "X"
Next
 
End Sub

Le principe; on regarde dans le tableau feuil1, on prend chaque valeur de la colonne A et on regarde ou placer la coche dans la feuil2. Sur ce principe on pourrait intégrer la création du tableau feuil2 dans se code, en rajoutant une ligne ou une colonne si la valeur n'est pas trouvé par Match.

++
Qwaz

**Jaymerry** · 18/01/2011, 22h43

Effectivement mon exemple a été rédigé un peu rapidement

Je suis en train de tester ton code, malheureusement quelque chose doit m'échapper car j'obtiens l'erreur suivante :
"Impossible de lire la propriété Match de la classe WorksheetFunction"

Merci beaucoup pour ton aide, je vois enfin le bout de ce problème.

**Qwazerty** · 19/01/2011, 08h26

Salut
Oui je dois mal utiliser le Match... ou alors sont fonctionnement n'est pas celui que j'en attend, ce qui revient au même ...

Voila un autre poste ou j'ai eu maille à partir avec Match, j'ai fini par le remplacer par une boucle. Je te laisses adapter le code à ton cas, si tu n'y arrives pas, je te filerai (ou quelqu'un d'autre qui suis le post) un coup de main ce soir.
++
Qwaz

**Jaymerry** · 30/01/2011, 21h28

Bonjour à tous,

Je reviens sur ce topic ; je n'ai pas réussi à traduire mon problème en boucle VBA acceptable en m'inspirant du lien énoncé dans le post d'avant :/

Auriez-vous une idée ?

Merci à tous pour votre aide,

Jaymerry

**Qwazerty** · 31/01/2011, 10h14

Salut
Regarde si ce code convient, comme dit plus haut, j'ai intégré la création du tableau directement dans le code,pour eviter de multiplier les boucle pour rien.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sub Macro()
Dim TheCell As Range, TheCellFindX As Range, TheCellFindY As Range
Dim NLigne As Integer, NCol As Integer
 
'On boucle sur le contenu de la colonne A
For Each TheCell In Feuil1.Range("A2", Feuil1.Cells(Rows.Count, "A").End(xlUp))
    'On recherche la veleur dans la colonne A de la feuille 2
    Set TheCellFindY = Feuil2.Columns("A").Find(TheCell, , , xlWhole, xlByColumns, , True, True)
    'On regarde si une valeur a ete trouvée
    If TheCellFindY Is Nothing Then 'Pas trouvé
        'On selectionne une cellule vide a la suite et On ajoute la valeur dans le tableau Feuille2
        Set TheCellFindY = Feuil2.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        TheCellFindY = TheCell
    End If
 
    'On cherche la valeur colonne B
    Set TheCellFindX = Feuil2.Rows(1).Find(TheCell.Offset(0, 1), , , xlWhole, xlByColumns, , True, True)
    'On regarde si valeur trouvée
    If TheCellFindX Is Nothing Then 'Pas trouvé
        'On pointe sur une nouvelle cellule et on ajoute la nouvelle valeur dans le tableau Feuille2
        Set TheCellFindX = Feuil2.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
        TheCellFindX = TheCell.Offset(0, 1)
    End If
    'Arrivé ici, TheCellFindx pointe dans tous les cas la bonne cellule de la ligne 1 du tableau FEuille2
    'et TheCellFindY pointe la bonne cellule de la colonne A
    'Il ne reste plus qu'a cocher l'intersection des 2
    Feuil2.Cells(TheCellFindY.Row, TheCellFindX.Column) = "X"
Next
 
'On vide les variables objet
Set TheCellFindY = Nothing
Set TheCellFindX = Nothing
Set TheCell = Nothing
 
End Sub

Par contre je n'ai pas utilisé de tableau, a voir si tu as une grande quantité de ligne a gérer.
Si tu souhaites en utiliser, essai de modifier le code.

++
Qwaz

**Jaymerry** · 16/02/2011, 19h27

Hello,

Merci pour ton aide.
Je me retrouve cependant dans la même problématique qu'énoncé précedemment avec un seul croisement de générer. Si une entrée possède deux croisements, ces derniers n'apparaitront pas

Merci à tous pour votre aide,

Jaymerry

Croiser lignes et colonnes SANS TCD

Macros et VBA Excel

Discussions similaires

Partager

Partager