XL 2019 Trouver le nom d’une colonne et reporter les erreurs dans un tableau de bord

[Homero18]

Bonjour à tous,

Dans le cadre d'un comparatif entre 2 tableaux, j'aimerais trouver une formule pour obtenir le nom de la colonne qui contient une erreur, ainsi que le nom de la personne. Et ainsi l'afficher sur le tableau de bord. Le but étant que dès lors que j'ai collé mes 2 tableaux, Excel calcule automatiquement s'il y a une erreur et qu'il la reporte sur le tableau de bord. Le résultat correct est 0, tout autre chiffre est un écart et donc une erreur qu'il faudra que je corrige. Le but étant que je n'ai plus besoin de fouiller mon tableau, du fait que j'ai le résultat instantanément sur mon tableau de bord

[GTN V24.xlsm|attachment](upload://f2NOuY04ApjNt9yG6758ASB9cto.xlsm) (230,8 Ko)

Ayant un peu cherché, j'ai trouvé une formule avec INDEX et EQUIV, sauf pour certains ça m'affiche bien le nom de la colonne, pour d'autres #N/A. Et ce alors même qu'il y a un écart (affiché en rouge). C'est également vrai pour la catégorie et le numéro de compte

Si la formule fonctionnait correctement, elle aurait dû afficher en cellule BN11 : "CSG-CRDS totally non-deductible EE". BO11 et BP11 sont exacts.

Ceci du fait qu'il y a un écart de 67.90. Or là, la cellule m'affiche #N/A. Et j'ai ce genre de problème sur tout mon tableau.


Je sollicite donc votre expertise, pour savoir si quelqu'un peut m'aider :

- à trouver la formule qui me permettrait d'avoir le bon résultat, au lieu de #N/A

Ce qui devrait donner ça si ça fonctionnait bien :

- faire en sorte que Excel reporte matricule-nom-montant-compte-element code, dans la bonne catégorie ("employee payments", "employer deductions" ou "employee deductions") de la feuille "Tableau de bord".

Ce qui devrait donner ça pour la première ligne si ça fonctionnait bien :

Mon fichier est en pièces jointes. Merci pour l'aide que vous m'apporterez. Au secours Obi-Wan Kenobi, vous êtes mon seul espoir!

 

[sylvanu]

Bonsoir Homero,
Déjà pour le premier point :
Votre cellule BM11 ne contient pas 67.90 mais 67.8999999999996 ( surement du à des arrondi de calculs fait par XL ). Pour le vérifier mettez BM11 en Nombre mais avec 15 chiffres significatifs.
Donc en BM11 mettez :

=ARRONDI(BK11-BL11;2)

Cela résout au moins le problème des #N/A.
Pour le reste, peut être plus tard.

 

[sylvanu]

AddOn :
Pour les #N/A résiduel, cela est du au fait que le RechercheV n'aboutit pas.
Donc pour l' "esthétisme" :

BL11:
=SIERREUR(RECHERCHEV(A15;'Journal recalcul'!$A$2:$L$379;4;FAUX);"")
BM11:
=SIERREUR(ARRONDI(BK11-BL11;2);"")
BN11:
=SIERREUR(INDEX($E$6:$BG$6;1;EQUIV(BM11;$A$11:$BG$11;0));"")
BO11:
=SIERREUR(INDEX($A$5:$BG$5;1;EQUIV(CI6;$A$11:$BG$11;0));"")
BP11:
=SIERREUR(INDEX($A$8:$BG$8;1;EQUIV(CI6;$A$11:$BG$11;0));"")

Le Sierreur évite l'affichage d'erreur, entre autres. ( ou le traitement de l'erreur )

SIERREUR - Support Microsoft

Comment utiliser la fonction IFERROR dans Excel pour intercepter et gérer les erreurs dans une formule.

support.microsoft.com

 

[Homero18]

C'est super ça! Vous m'avez appris quelque chose! Merci beaucoup

Vivement la suite

 

[sylvanu]

Pour le second point, perso, je ne sais pas faire en formules, mais en VBA c'est faisable .... si vous acceptez le VBA.
Il faudrait "améliorer" votre PJ pour que quelques EMPLOYER DEDUCTIONS et EMPLOYEE DEDUCTIONS apparaissent. Pour les tests c'est plus probant.

 

[Homero18]

Oui pourquoi pas pour le VBA

J'ai essayé de tenir compte du point amélioration, dites moi si c'est mieux

 

[sylvanu]

Bonjour,
Un essai en PJ.
Dans la colonne BO de votre Pj il n'y a que des "Employee Payments" donc impossible de tester les autres catégorie.
A défaut d'explications, j'ai supposé que Montant c'était Ecart et Element code c'était Matricule.
Sinon modifiez le code il est très simple :

Public Ligne%, DL%, Catégorie$, Colonne%
Sub MiseAjourTDB()
    Application.ScreenUpdating = False
    [C11:T10000].ClearContents
    With Sheets("Sheet1")
        DL = .Range("A65500").End(xlUp).Row
        Catégorie = "EMPLOYEE PAYMENTS": Ligne = 11: Colonne = 3    ' Init catégorie et XY pour insertion
        Insère Catégorie
        Catégorie = "EMPLOYER DEDUCTIONS": Ligne = 11: Colonne = 9
        Insère Catégorie
        Catégorie = "EMPLOYEE DEDUCTIONS": Ligne = 11: Colonne = 15
        Insère Catégorie
    End With
End Sub
Sub Insère(Quoi)
    Quoi = LCase(Quoi)      ' En minuscules pour éviter les erreurs de comparaison
    With Sheets("Sheet1")
        For L = 11 To DL
            If LCase(.Cells(L, "BO")) = Quoi Then
                Cells(Ligne, Colonne + 0) = .Cells(L, "B") '  Insertion Nom
                Cells(Ligne, Colonne + 1) = .Cells(L, "BM") ' Insertion Ecart
                Cells(Ligne, Colonne + 2) = .Cells(L, "BN") ' Insertion Compte
                Cells(Ligne, Colonne + 3) = .Cells(L, "A") '  Insertion Matricule
                Ligne = Ligne + 1
            End If
        Next L
    End With
End Sub

 

[Homero18]

Super! Merci beaucoup pour ton aide. Tu déchires. En modifiant un peu ce que tu m'as donné j'ai réussi