Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

[PyGidiuM]

Bonjour à tous,

Adepte de ce forum je n'ai pourtant pas réussi à trouver mon bonheur.

J'explique mon problème, j'ai deux tableaux:
Tableau 1: de A à E sur le fichier joint
Tableau 2 : offres retenues: colonne G à J

Dans le tableau 1 j'ai toutes les offres, dans le tableau 2 seulement les offres retenues.

j'aimerais que les lignes du tableau 2 s’aligne sur celle du tableau 1 en fonction des donnés de la colonne D du tableau 1 et de la colonne I du tableau 2

Merci infiniment de votre aide

Pygi

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Bonjour PyGidiuM, bienvenue sur XLD,

Pas compris votre "en fonction des données ... colonne D... colonne I...".

Dans le fichier joint j'ai recherché la correspondance exacte des 4 colonnes des 2 tableaux sources.

Voyez les formules en colonnes K (matricielle) et L, ainsi que les 2 tableaux des résultats colonnes P:S et colonnes W:Z.

Il peut être utile de masquer les colonnes F:M.

Bonne soirée.

Edit : bah je m'étais trompé de colonne dans la formule du tableau P:S

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Re,

Cela dit il me paraît amplement suffisant de colorer dans le 1er tableau les lignes qui se trouvent dans le 2ème.

Mise en forme conditionnelle (MFC) sur la plage A2:E4326 avec la formule :

=NB.SI.ENS($G:$G;$A2;$H:$H;$B2;$I:$I;$C2;$J:$J;$D2)

Sur les versions antérieures à Excel 2007 NB.SI.ENS n'existe pas, il faut utiliser SOMME sur des plages délimitées.

Fichier joint.

A+

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Re,

Noter que la validation d'une cellule dans le 1er tableau entraîne le recalcul de toutes les formules.

Cela prend du temps : 6,8 secondes (fichier du post #2) et 2,3 secondes (fichier du post #3).

Bonne nuit.

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Bonjour PyGidiuM, le forum,

Il y a 531 lignes en doublon dans le 1er tableau et 5 dans le 2ème.

Le fichier joint les met en évidence, par formules (colonnes F et K) et par MFC.

Il ne serait pas très difficile de les supprimer.

A+

 

[PyGidiuM]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Merci infiniment à vous trois, c'est exactement ce dont javais besoin, et vous avez su répondre à toutes mes problématiques, un vrai travail d'équipe .
Bien que ma demande manquait de précisions.

Encore merci, je vous souhaite d'excellentes fêtes de fin d'année, et une très prospère année 2016.

A très bientôt

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Re,

Merci infiniment à vous trois(...), un vrai travail d'équipe .

Bah en effet j'arrive à me démultiplier

A+

 

[PyGidiuM]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

, ça m'apprendra de ne pas avoir vérifié.
Merci à toi alors, désolé de cette petite erreur

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Bonsoir PyGidiuM, Le forum,

A tous je souhaite une excellente année 2016.

Je reviens sur ce fil pour une précision importante.

NB.SI.ENS ne compte pas les lignes dont une ou plusieurs colonnes sont vides.

Pour qu'elles soient comptées utiliser SOMMEPROD (ou SOMME dans la MFC).

Donc utilisez ces fichiers (2).

A+

 

[job75]

Re : Comment "matcher" deux tableaux en fonction du contenu de deux Colonnes

Bonjour PyGidiuM, le forum,

Une dernière solution qui donne la possibilité de supprimer par VBA dans les 2 tableaux :

- les espaces superflus

- les lignes en doublons

Sub SupprEspace()
Dim t, i&, j%, x$, k%, n&, p&
t = [A2:E4326]
For i = 1 To UBound(t)
  For j = 1 To 4
    x = Application.Trim(t(i, j)) 'SUPPRESPACE
    k = Len(t(i, j)) - Len(x)
    If k Then n = n + 1: p = p + k: t(i, j) = x
Next j, i
[A2:E4326] = t
MsgBox n & " cellules traitées et " & p & " espaces supprimés dans Tableau 1"
t = [G2:J1664]: n = 0: p = 0
For i = 1 To UBound(t)
  For j = 1 To 4
    x = Application.Trim(t(i, j)) 'SUPPRESPACE
    k = Len(t(i, j)) - Len(x)
    If k Then n = n + 1: p = p + k: t(i, j) = x
Next j, i
[G2:J1664] = t
MsgBox n & " cellules traitées et " & p & " espaces supprimés dans Tableau 2"
End Sub

Sub SupprDoublon()
Dim t, d As Object, i&, x$, n&
t = [A2:E4326]
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
For i = 1 To UBound(t)
  x = t(i, 1) & t(i, 2) & t(i, 3) & t(i, 4)
  If x <> "" And Not d.Exists(x) Then
    d(x) = ""
    n = n + 1
    t(n, 1) = t(i, 1): t(n, 2) = t(i, 2): t(n, 3) = t(i, 3): t(n, 4) = t(i, 4)
  End If
Next i
If n Then [A2:E4326].Resize(n) = t
Range("A" & n + 2 & ":F" & Rows.Count).Delete xlUp
MsgBox 4325 - n & " lignes doublons supprimées dans Tableau 1"
t = [G2:J1664]: d.RemoveAll: n = 0
For i = 1 To UBound(t)
  x = t(i, 1) & t(i, 2) & t(i, 3) & t(i, 4)
  If x <> "" And Not d.Exists(x) Then
    d(x) = ""
    n = n + 1
    t(n, 1) = t(i, 1): t(n, 2) = t(i, 2): t(n, 3) = t(i, 3): t(n, 4) = t(i, 4)
  End If
Next i
If n Then [G2:J1664].Resize(n) = t
Range("G" & n + 2 & ":J" & Rows.Count).Delete xlUp
MsgBox 1663 - n & " lignes doublons supprimées dans Tableau 2"
With ActiveSheet.UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Edit : ajouté l'actualisation de la barre de défilement verticale.

A+