[résolu] Fusion de deux matrices en une

[Arnaud Esk]

Bonjour à la communauté.

Encore une fois, je viens quérir l'assistance d'Excel Download sur une problématique Excel.

En effet, comme vous le trouverez illustré sur la pièce jointe, je cherche à croiser deux matrices pour n'en donner qu'une. Je pense que la solution est sous mon nez avec une formule matricielle mais je ne la trouve pas par moi même et mes mots clefs ne doivent pas être les bons pour Google.

Saurez vous m'aider ? [edit suite à réponse ci-dessous : sans VBA si possible, merci]
D'avance, je vous remercie de votre participation et aide.

Arnaud.

 

[Dranreb]

Re : Fusion de deux matrices en une

Bonjour.

En VBA, à l'activation de la feuille, à l'aide d'un dictionnaire arborescent.

 

[phlaurent55]

Re : Fusion de deux matrices en une

Bonjour à tous,

puisque je l'avais réalisé, je poste

à+
Philippe

 

[Arnaud Esk]

Re : Fusion de deux matrices en une

Dranreb, Philippe, je vous remercie tout d'abord de votre réactivité et de la pertinence de vos réponses.

Néanmoins, bien qu'elles marchent à merveille et qu'une partie de moi veuille se mettre au VBA vu la performance de vos réponses (mise à jour automatique des listes sans besoin de gestionnaire des noms, NBVAL, ... ), j'aurais besoin, si possible, d'une solution sans macro (pour la pérennisation dans l'entreprise).

Encore une fois merci à vous deux

 

[phlaurent55]

Re : Fusion de deux matrices en une

Re,

..........., j'aurais besoin, si possible, d'une solution sans macro (pour la pérennisation dans l'entreprise).

Encore une fois merci à vous deux

......... Désolé, sans macro, je jette l'éponge

à+
Philippe

 

[ROGER2327]

Re : Fusion de deux matrices en une

Bonjour à tous.

Je ne sais pas non plus faire ça par une formule.

Un autre code, paramétré, dans le classeur joint.​

Sub toto()
Dim i&, j&, k&, l&, n&, pn, ut, v(), Dat()

'Paramètres :
Dim Dat1$, Dat2$, Dat3$
Const NL1& = 3001               'Nombre maximum de lignes à prendre en compte dans la 1ère feuille de données. ( > 0 et <= Rows.Count )
Const NL2& = 5001               'Nombre maximum de lignes à prendre en compte dans la 2ème feuille de données. ( > 0 et <= Rows.Count )
Const NC2& = 4                  'Nombre de colonnes à prendre en compte dans la 2ème feuille de données. ( > 0 et <= Columns.Count-1 )
Const VN$ = "A619_PP_ROGER2327" 'Chaîne de caractères arbitraire acceptable par le gestionnaire de noms mais étrangère à son contenu.
Dat1$ = "'" & Feuille01.Name & "'"  '1ère feuille de données.
Dat2$ = "'" & Feuille02.Name & "'"  '2ème feuille de données.
Dat3$ = Feuille03.Name              'feuille de synthèse.
'

    On Error Resume Next
    i = Names(VN).Index: If Err.Number = 0 Then MsgBox "Modifiez la constante VN !": End
    On Error GoTo 0
    v = Array(Array(Dat1, NL1, 2), Array(Dat2, NL2, NC2))
    ReDim Dat(UBound(v))
    For i = 0 To UBound(v)
        Names.Add Name:=VN, RefersToR1C1:="=OFFSET(" & v(i)(0) & "!R1C1,,,MAX((" & v(i)(0) & "!R1C1:R" & v(i)(1) & "C1<>"""")*ROW(" & v(i)(0) & "!R1:R" & v(i)(1) & "),1)," & v(i)(2) & ")"
        Dat(i) = Range(VN).Value
    Next
    Names(VN).Delete

    l = UBound(Dat(1))
    For i = 1 To UBound(Dat(0)): For j = 1 To l
        If Dat(0)(i, 2) = Dat(1)(j, 1) Then n = n + 1
    Next j, i
    n = n - (n = 0)
    ReDim v(1 To n, NC2)
    n = 0
    For i = 1 To UBound(Dat(0))
        pn = Dat(0)(i, 1)
        If Not IsEmpty(pn) Then
            ut = Dat(0)(i, 2)
            For j = 1 To l
                If ut = Dat(1)(j, 1) Then n = n + 1: v(n, 0) = pn: v(n, 1) = ut: For k = 2 To NC2: v(n, k) = Dat(1)(j, k): Next
            Next
        End If
    Next

    k = (Rows.Count + n - Abs(Rows.Count - n)) / 2
    With Worksheets(Dat3).[A1]
        .CurrentRegion.ClearContents
        .Resize(k - (k = 0), NC2 + 1).Value = v
    End With
    If n > k Then MsgBox n - Rows.Count & " ligne" & IIf(n - Rows.Count > 1, "s n'ont pu être affichées.", " n'a pu être affichée.")
End Sub

• Testé avec ~1300 lignes dans la première feuille et ~3000 dans la deuxième.
• Ne plante pas si, par inadvertance, l'une ou l'autre des feuilles est vide.
• Ne plante pas si le nombre de lignes du résultat dépasse le nombre de lignes d'une feuille.
• Permet la modification des noms d'onglet sans modification du code.
  (La modification du code est nécessaire si on modifie le nom de code des feuilles.)

À Arnaud Esk : pourquoi une solution avec VisualBasic n'est-elle pas pérenne ?
​

Bonne journée.


ℝOGER2327
#7584

Mercredi 25 Absolu 142 (Nativité de Sa Magnificence Opach - fête Suprême Quarte)
11 Vendémiaire An CCXXIII, 0,3407h - pomme de terre
2014-W40-4T00:49:03Z

 

[mapomme]

Re : Fusion de deux matrices en une

Bonjour Arnaud Esk, le forum ,

Un essai par formules:
.

• on utilise 4 colonnes (A à D) masquées pour des formules auxiliaires.
• les blocs de couleurs indiquent des zones dont la formule de la première ligne de la zone peut-être recopiée vers le bas.
• on a défini 6 noms dynamiques: Noms1, Unite1, Unite2, ItemA, ItemB, ItemC afin de prendre en compte les ajouts ou suppressions de lignes dans les deux tableaux sources
• les formules des colonnes G,H et I sont des formules matricielles à valider par Ctrl+Maj+Entrée.
• si le résultat doit renvoyer plus de 994 lignes, il faudra rallonger le tableau de Feuil3 en recopiant les formules (yc colonnes masquées) et modifier la formule de la colonne C en remplaçant la borne sup. 995 par le numéro de la dernière ligne du tableau rallongé)

Edit: v1a -> correction d'une borne à 995 dans la formule de la colonne C

 

[Arnaud Esk]

Re : Fusion de deux matrices en une

Roger, mapomme, merci

J'ai testé vos deux propositions dans tous les sens, elles marchent formidablement bien. Comme évoqué, mon cahier des charges préférait les formules donc je vais prendre pour mon usage la solution de mapomme (en plus je viens d'apprendre qu'on peut faire un décaler de =decaler(plage;1;0;nbval(plage). Moi qui me tapait à chaque fois la formule ! (merci)

En réponse à ROGER2327, l'adjectif "pérenne" n'est pas adapté à ce que je voulais dire.
Je ne suis moi même pas pérenne dans mon entreprise. Entreprise de 600 personnes où la notion de tableau croisé dynamique est familière à 5% des salariés.
Dans ces conditions, je préfère avoir une solution dont l'entretien sera plus facile qu'une solution en VBA pour une personne non initiée. (mais j'utilise pour mon propre usage des solutions macro pour certaines applications)

Dans tous les cas, je tiens à vous remercier tous les quatre encore une fois pour avoir pris du temps pour ma requête ! Donc grand merci !