Comment réconcilier deux exports de clients ou de SKU par ID : un workflow pratique

1. Préparez vos exports : normalisation et alignement des en-têtes

Avant de comparer les enregistrements, identifiez la colonne clé dans chaque export et rendez sa représentation cohérente. Les noms d'en-tête n'ont pas besoin d'être identiques pour une simple comparaison d'appartenance de valeurs, mais vous devez savoir quelle colonne contient le même type d'ID des deux côtés.

Normalisez les espaces de début et de fin, la casse lorsque c'est approprié, les ID vides et les types de données. Conservez les identifiants tels que 00123 sous forme de texte. Les colonnes descriptives supplémentaires peuvent rester dans les fichiers source, mais copiez uniquement les deux colonnes d'ID dans une étape de comparaison ciblée.

N'exécutez pas de commande de suppression d'espaces ligne par ligne sur un fichier CSV entre guillemets : les champs entre guillemets peuvent contenir des délimiteurs ou des sauts de ligne. Utilisez une importation de tableur, Power Query ou un véritable analyseur CSV pour les fichiers structurés.

1. Ouvrez les deux exports dans un tableur ou un éditeur de texte.
2. Standardisez les en-têtes de colonnes : mêmes noms, même casse, pas d'espaces supplémentaires.
3. Supprimez toutes les colonnes temporaires ou non pertinentes qui ne font pas partie de la comparaison.
4. Assurez-vous que la colonne ID (par exemple, CustomerID, SKU) est formatée en texte pour éviter les problèmes d'arrondi numérique.
5. Nettoyez les données à l'aide de =TRIM() ou d'un nettoyeur de liste pour supprimer les espaces supplémentaires.

=TRIM(A2)

2. Identifiez et gérez les ID en double

Les ID en double dans l'un ou l'autre export peuvent rendre une réconciliation biunivoque ambiguë. Une recherche peut indiquer qu'un ID existe tout en cachant le fait qu'il apparaît deux fois d'un côté et une fois de l'autre.

Auditez les ID en double avant la comparaison. Ne supprimez pas automatiquement des enregistrements entiers simplement parce que l'ID se répète : plusieurs lignes peuvent être légitimes, comme plusieurs commandes pour un même client. Résolvez la règle métier, ajoutez un sous-identifiant si nécessaire, et enregistrez toute décision de consolidation.

1. Comptez le nombre total de lignes dans chaque fichier.
2. Sélectionnez la colonne ID et effectuez une vérification des doublons (par exemple, =COUNTIF(plage, B2)>1).
3. Enregistrez le nombre de doublons et décidez s'il faut conserver la première occurrence ou marquer pour examen manuel.
4. Supprimez ou consolidez les doublons pour créer une liste d'ID uniques propre pour chaque export.

• Astuce : Dans Excel, les tableaux croisés dynamiques peuvent rapidement lister les ID en double avec leur nombre.
• Attention : Si votre fichier contient des ID en double légitimes avec des données différentes (par exemple, plusieurs commandes pour le même client), vous devez les traiter comme des enregistrements séparés ; envisagez d'ajouter un sous-identifiant unique.

SELECT CustomerID, COUNT(*) FROM Source GROUP BY CustomerID HAVING COUNT(*) > 1;

=IF(COUNTIF($B$2:$B$1000, B2)>1, "Duplicate", "Unique")

3. Comparez l'appartenance des ID dans les deux sens

Avec les colonnes d'ID normalisées, identifiez les clés qui existent uniquement dans Source et celles qui existent uniquement dans Cible. Il s'agit d'une comparaison d'appartenance bidirectionnelle, similaire aux parties anti-jointure d'une jointure externe complète.

Dans Excel, utilisez XLOOKUP, RECHERCHEV, EQUIV ou Power Query. Dans CompareTwoLists, collez les deux colonnes d'ID extraites dans Compare Two Columns et examinez les correspondances, les valeurs uniquement dans la première, les valeurs uniquement dans la seconde, ou l'union. L'outil compare les valeurs ; il ne fusionne pas les enregistrements complets ni ne fait correspondre les en-têtes.

Après avoir trouvé les ID manquants, revenez aux exports originaux pour récupérer et examiner les enregistrements complets correspondants.

1. Créez une nouvelle colonne dans Source appelée 'In_Target' et utilisez RECHERCHEV pour vérifier si chaque ID existe dans Cible.
2. De même, vérifiez les ID de Cible par rapport à Source.
3. Filtrez chaque liste pour les ID manquants et exportez-les sous forme de rapports d'enregistrements manquants séparés.
4. Examinez les enregistrements manquants : s'agit-il de différences légitimes ou d'anomalies ? Documentez les conclusions.

=IF(ISNA(VLOOKUP(A2, Target!$A$2:$A$5000, 1, FALSE)), "Missing in Target", "Present")

SELECT s.* FROM Source s LEFT JOIN Target t ON s.CustomerID = t.CustomerID WHERE t.CustomerID IS NULL;

SELECT t.* FROM Target t LEFT JOIN Source s ON t.CustomerID = s.CustomerID WHERE s.CustomerID IS NULL;

4. Détectez les enregistrements modifiés

Après avoir identifié les ID présents dans les deux exports, comparez les champs importants pour chaque enregistrement apparié, tels que le statut du client, la description du produit, le prix ou la quantité en stock. Normalisez le type de données de chaque champ et décidez comment les valeurs vides, la casse, les dates et la tolérance numérique doivent être traitées avant de les comparer.

Utilisez une jointure Excel ou Google Sheets, une fusion Power Query, une JOIN SQL ou une fusion de dataframe pour aligner les enregistrements complets par ID. L'outil Compare Columns de CompareTwoLists est utile pour les deux listes d'ID extraites, mais il ne joint pas les enregistrements complets ni ne compare automatiquement chaque champ d'un CSV.

1. Créez une liste combinée des ID présents dans les deux exports (en utilisant les résultats de l'étape 3).
2. Pour chaque ligne, comparez champ par champ en utilisant SI(ChampSource=ChampCible, "Correspondance", "Différence") ou une formule matricielle.
3. En option, concaténez tous les champs clés et hachez-les pour vérifier l'équivalence dans une cellule.
4. Extrayez les lignes qui ont au moins une différence de champ dans un rapport 'Modifications'.

• Lors de la comparaison de valeurs numériques, méfiez-vous des différences de précision ou de format (par exemple, 10,00 vs 10). Convertissez les deux en un type cohérent.
• Pour les fichiers volumineux avec de nombreuses colonnes, concentrez-vous sur les colonnes critiques pour votre logique métier ; excluez les champs non pertinents comme les horodatages qui différeront toujours.

=IF(D2=E2, "", "DIFF")

SELECT s.CustomerID, s.CustomerName AS SourceName, t.CustomerName AS TargetName FROM Source s INNER JOIN Target t ON s.CustomerID = t.CustomerID WHERE s.CustomerName <> t.CustomerName OR (s.CustomerName IS NULL AND t.CustomerName IS NOT NULL) OR (s.CustomerName IS NOT NULL AND t.CustomerName IS NULL);

5. Construisez des preuves d'audit : créez un journal des modifications

Une fois que vous savez quels champs ont changé pour quels ID, créez un journal des modifications vérifiable avec l'ID d'enregistrement, le nom du champ, la valeur source, la valeur cible, le statut et les notes de révision. Ce rapport structuré donne aux examinateurs des preuves qu'ils peuvent filtrer, annoter et approuver.

Construisez le journal avec des formules de tableur, Power Query, SQL ou un workflow dataframe après avoir joint les enregistrements par ID. La sortie de la comparaison de listes peut soutenir la partie des ID manquants de l'audit, mais un journal des modifications complet au niveau des champs doit provenir d'une comparaison de données structurées.

1. Pour chaque ID qui a des modifications, créez une ligne par champ modifié.
2. Remplissez les colonnes : RecordID, FieldName, SourceValue, TargetValue, Status.
3. Utilisez la mise en forme conditionnelle pour mettre en évidence les différences (par exemple, rouge pour les non-concordances).
4. Ajoutez une feuille récapitulative qui montre les comptes de correspondances, de modifications, de manquants de chaque côté.

• Un journal d'audit peut être directement importé dans des outils de gestion de projet pour les actions à entreprendre.
• Conservez une feuille séparée pour les hypothèses documentées (par exemple, 'Différences d'horodatage ignorées').
• Incluez toujours une ligne d'en-tête et assurez-vous d'avoir des horodatages pour chaque exécution d'audit.

=IF(Sheet1!D2<>Sheet2!D2, "Changed", "")

6. Validez avec les statistiques de liste

Terminez par des vérifications de comptage. Après la normalisation et l'examen des doublons, comparez le nombre total de lignes, les ID non vides, les ID uniques, les groupes de doublons et les ID vides de chaque côté. Ces totaux aident à révéler un filtre oublié ou une suppression accidentelle de doublons.

L'outil List Statistics rapporte le nombre de lignes, les lignes non vides, les valeurs uniques, les groupes de doublons, les lignes vides et la longueur moyenne du texte. Les sommes numériques et la réconciliation au niveau des champs relèvent toujours d'Excel, SQL ou d'un autre outil de données structurées.

1. Enregistrez le nombre total, non vide, unique, de groupes de doublons et de vides pour chaque liste d'ID.
2. Confirmez que les ID uniques appariés plus les ID uniques uniquement dans Source sont égaux au nombre d'ID uniques de Source.
3. Confirmez que les ID uniques appariés plus les ID uniques uniquement dans Cible sont égaux au nombre d'ID uniques de Cible.
4. Enquêtez sur toute différence inexpliquée avant l'approbation.

• Utilisez SQL COUNT, COUNT(DISTINCT ...) et GROUP BY pour les vérifications côté base de données.
• Utilisez les sommes de tableur séparément lorsque les totaux numériques doivent également être réconciliés.

=COUNTIF(K:K, "Match")

SELECT 'SourceCount' AS Metric, COUNT(*) AS Value FROM Source UNION ALL SELECT 'TargetCount', COUNT(*) FROM Target UNION ALL SELECT 'Matched', COUNT(*) FROM Source s INNER JOIN Target t ON s.ID = t.ID;

7. Révision sécurisée et validation

La dernière étape consiste à faire examiner la réconciliation par une deuxième personne ou un outil indépendant pour vérifier son exhaustivité et son exactitude. Cela réduit le risque de biais de confirmation. Examinez la liste des enregistrements manquants—s'agit-il d'exclusions valides ? Vérifiez par sondage un échantillon des enregistrements modifiés pour vous assurer que la logique de comparaison de champs était sans erreur. Pour les réconciliations à enjeux élevés, envisagez d'inverser l'ordre (échanger Source et Cible) pour voir si les mêmes différences sont signalées.

Créez une feuille de validation qui capture la date, le nom du réviseur, le nombre total de différences (par catégorie) et les éventuelles exceptions. L'objectif est d'avoir un rapport de qualité décisionnelle qui permet aux gestionnaires d'approuver en toute confiance l'étape suivante (par exemple, rechargement des données, correction des écarts).

1. Préparez un rapport de synthèse de réconciliation avec les indicateurs clés : nombre total d'enregistrements de chaque côté, appariés, non appariés, modifiés, inchangés.
2. Demandez à un collègue d'être un deuxième réviseur ; demandez-lui de répéter la comparaison en utilisant une méthode différente (par exemple, une vérification manuelle d'un échantillon aléatoire).
3. Documentez toutes les limitations connues, comme les colonnes exclues ou les décisions de correspondance floue.
4. Stockez le rapport final avec les exports bruts pour référence future.

• Utilisez le contrôle de version pour les exports : nommez les fichiers avec des dates et des suffixes comme _source-v1, _target-v2.
• Si la réconciliation échoue aux vérifications initiales, revenez à l'étape 1 et affinez la normalisation ou la gestion des doublons.