Macro affichage de correspondances dans un nouvel onglet

Bonjour,

Ne maîtrisant pas la création de macro (seulement l'adaptation d'une macro à mes colonnes, lignes et cellules), je m'adresse au forum afin d'avoir de l'aide sur une réalisation que je souhaiterais faire.

J'ai 2 onglets sur un fichier. Une liste de personne et une liste de métier en l’occurrence.

Les 2 listes ont un point commun qui est la colonne "Code" dans mon exemple. Je souhaite faire des rapprochements de l'onglet "salarié" à l'autre onglet "métier" en affichant, dans un nouvel onglet (onglet "Résultats").

J'ai ajouté l'onglet "résultats" que je souhaiterais avoir lors d'une recherche depuis l'onglet "salarié".

Dans cet exemple, la personne RRR disparaitrait de ma recherche car elle n'a aucune correspondance dans l'onglet "métier". La personne ggg aurait 3 lignes crées (ou dupliquées) avec, à la suite dans les autres colonnes, le détail des correspondances trouvées dans l'onglet "métier" et ainsi de suite pour chaque salarié ou la correspondance est supérieur à 0. Attention, le code métier est le même mais les informations des autres colonnes diffèrent. il faudrait que toutes ces informations apparaissent dans les colonnes suivantes.

J'espère avoir été clair dans mes explications...

Merci par avance

Cdt

10exemple.xlsx (11.35 Ko)

Saucisson

Bonjour,

Une macro qui va utiliser des formules de recherche ?

Je vois pas trop l'intérêt de se compliquer à ce point.

Un tableau croisé dynamique ou alors l'information métier ajouté par formule dans le premier onglet suffirait simplement à

filtrer les infos souhaitées.

Bonjour,

Merci pour votre aide et votre retour rapide.

Les informations du code métier (exemple : adresse) ne sont pas les mêmes bien que le code métier soit identique (un code métier peut avoir plusieurs adresses). Une recherche serait suffisante si un code métier avait les mêmes informations derrière mais ce n'est pas le cas dans mon exemple. Peut être avec index et equiv mais le nombre de correspondance peut varier énormément...

Je vais essayer d'illustrer ma demande :

En fait, monsieur GGG est boucher (code G4280) à Tourcoing, je voudrais avoir la liste des postes de boucher disponibles en France. Dans mon exemple, il y a 3 postes disponibles (cf nb correspondance de l'onglet "salarié") au code G4280 à l'adresse 1, 4 et 7 (onglet "métier"). Je voudrais donc créer une nouvelle base (un nouvel onglet) "résultat" qui afficherait 3 lignes (3 correspondances trouvées) avec GGG, prénom, adresse, code métier. Puis, sur chacune de ces 3 lignes, les 3 informations différentes (détail, adresse, etc...) de l'onglet métier. Cela pour chaque salarié ou il y a une correspondance ou plus...

Je ferai d'autres traitements sur cette base par la suite.

Voilà j'espère que mon exemple donne un peu plus de lisibilité à ma demande.

Encore merci pour votre aide

Bonjour à tous,

Supprimez dans vos 2 feuilles la dernière colonne contenant vos formules, ce n'est pas indispensable.

Essayez ceci :

Option Explicit
Sub test()
    Dim a, b, c(), i As Long, ii As Long, iii As Byte, n As Long
    a = Sheets("Salariés").Range("A1").CurrentRegion.Value
    b = Sheets("Métier").Range("A1").CurrentRegion.Value
    ReDim c(1 To (UBound(a, 1) - 1) * (UBound(b, 1) - 1) + 1, 1 To 8)
    n = 1
    For i = 2 To UBound(a, 1)
        For ii = 2 To UBound(b, 1)
            If a(i, 4) = b(ii, 1) Then
                For iii = 1 To UBound(a, 2)
                    c(n, iii) = a(i, iii)
                Next
                For iii = 1 To UBound(b, 2)
                    c(n, iii + 4) = b(ii, iii)
                Next
                n = n + 1
            End If
        Next
    Next
    With Sheets.Add
        .Cells(1, 1).Resize(, UBound(a, 2)).Value = Application.Index(a, 1, 0)
        .Cells(1, 5).Resize(, UBound(b, 2)).Value = Application.Index(b, 1, 0)
        .Cells(2, 1).Resize(n, UBound(c, 2)).Value = c
        With .Cells(1, 1).CurrentRegion
            .VerticalAlignment = xlCenter
            .BorderAround Weight:=xlThin
            .Borders(xlInsideVertical).Weight = xlThin
            With .Rows(1)
                .Font.Size = 11
                .Interior.Color = 9359529
                .BorderAround Weight:=xlThin
                .HorizontalAlignment = xlCenter
            End With
            .Columns.AutoFit
        End With
    End With
End Sub

klin89

Bonjour,

Votre macro fonctionne parfaitement ! Merci!

J'ai ajouté en dim j As Long, jj As Long, k As Long en plus pour paramétrer les numéros des colonnes où se trouvent les informations cherchées. J'ai repris cela dans le code.

J'ai juste une interrogation en ligne 5 de votre code :

ReDim c(1 To (UBound(a, 1) - 1) * (UBound(b, 1) - 1) + 1, 1 To 8) => que signifie le 8 ? Le nombre total de colonne du fichier ?

Bonjour,

La macro fonctionne bien mais maintenant j'ai énormément de données à matcher.

Il y aura 250 000 match retrouvés et j'ai une erreur 7 mémoire insuffisante. Mon premier onglet comporte 39 colonnes et 6000 lignes et le second tableau il y a 20 colonnes et 5000 lignes.

Savez-vous s'il y a une optimisation possible de la macro ? Merci par avance

Je vous mets la macro :

NB : j'ai modifié des éléments de la macro car cela ne fonctionnait plus à un moment donné. La fin de la macro est simplement de la mise en forme que j'ai enlevé.

Option Explicit
Sub Correspondances()
Dim a, b, c(), i As Long, ii As Long, iii As Byte, n As Long, j As Long, jj As Long, k As Long
a = Sheets("CANDIDATS").Range("A1").CurrentRegion.Value
b = Sheets("OFFRES").Range("A1").CurrentRegion.Value
j = 17 'numéro colonne département candidats
jj = 10 ' numéro colonne département offres
k = 39 'nombre de colonne onglet candidats

ReDim c(1 To (UBound(a, 1) - 1) * (UBound(b, 1) - 1) + 1, 1 To 60) => erreur
n = 1
For i = 2 To UBound(a, 1)
For ii = 2 To UBound(b, 1)
If a(i, j) = b(ii, jj) Then
For iii = 1 To UBound(a, 2)
c(n, iii) = a(i, iii)
Next
For iii = 1 To UBound(b, 2)
c(n, iii + k) = b(ii, iii)
Next
n = n + 1
End If
Next
Next
With Sheets.Add
.Name = "MATCH"
'.Cells(1, 1).Resize(, UBound(a, 2)).Value = Application.Index(a, 1, 0)

Sheets("CANDIDATS").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MATCH").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' .Cells(1, k + 1).Resize(, UBound(b, 2)).Value = Application.Index(b, 1, 0)

Sheets("OFFRES").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("MATCH").Select
Range("AN1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

.Cells(2, 1).Resize(n, UBound(c, 2)).Value = c

End With

suite macro mise en forme

End Sub

Bonjour Saucisson_47,

Remplace ceci :

ReDim c(1 To (UBound(a, 1) - 1) * (UBound(b, 1) - 1) + 1, 1 To 8)

par cela :

ReDim c(1 To 250 000, 1 To 59)

et teste.

klin89

Bonjour et merci pour votre réponse.

Donc si je comprends bien vous avez écrit en "dur" la plage à dimensionner. donc si un jour la plage s'agrandit j'augmente le 250 001 et de même sur le 59 représentant mon nombre de colonnes.

Je viens de lancer la macro. Elle tourne et je n'ai pas d'erreur au lancement.

Je vous tiens au courant.

Re Saucisson_47,

Dans ton cas :

UBound(a, 1) - 1) * (UBound(b, 1) - 1) équivaut à 30 000 000 ----------> 6000 x 5000, c'est pour cela que ça coinçait.

Comme tu a estimé à 250 000 les correspondances, j'ai calibré la 1ère dimension en dur à 250 000.

Mais dans le cas extrême, on pourrait ressortir 30 000 000 de correspondances.

Après, si ça coince sur le dimensionnement, mets un espion sur i et sur n et recalibre la variable tableau.

klin89

Rechercher des sujets similaires à "macro affichage correspondances nouvel onglet"