Il n’est pas facile de fournir une formule sur mesure pour comparer des tableaux et supprimer les doublons dans Google Sheets. En effet, les besoins peuvent varier d’une personne à l’autre.
Dans cet article, je vais vous partager une formule qui vous aidera à comparer deux tableaux et à supprimer les doublons dans Google Sheets. Cette astuce peut être utile de la manière suivante :
Supposons que vous ayez deux tableaux dans un fichier Google Sheets contenant les détails des factures émises et des paiements reçus. Les onglets correspondants sont « COMPTES À RECEVOIR » et « PAIEMENTS COLLECTÉS ».
Comment faire correspondre les numéros de facture et les supprimer ?
Vous pouvez comparer les deux tableaux pour trouver les doublons et les supprimer en utilisant une formule dans Google Sheets.
Voyons d’abord comment préparer les données d’exemple.
Données d’exemple pour la comparaison de deux tableaux et la suppression des doublons
Tableau 1 : COMPTES À RECEVOIR
J’ai les enregistrements suivants dans l’onglet Comptes à Recevoir.
Tableau 2 : PAIEMENTS COLLECTÉS
Je n’ai qu’un seul enregistrement dans l’onglet Paiements Collectés.
Vous pouvez maintenant voir comment comparer ces deux tableaux pour trouver les doublons et les supprimer.
Comme vous pouvez le constater, le numéro de facture II000025 se répète dans les deux tableaux. Nous pouvons le gérer de deux façons différentes !
- Supprimer toutes les occurrences des lignes correspondantes.
- Garder un seul enregistrement de la ligne correspondante.
Comment faisons-nous correspondre les enregistrements ?
Cela dépend de vous, que vous ne considériez que le numéro de facture pour la correspondance (méthode A) ou la ligne totale (méthode B).
Formules pour comparer deux tableaux et supprimer les doublons
1. Supprimer toutes les occurrences de la ligne correspondante
Ligne totale correspondante (méthode B) :
La formule suivante supprime les lignes correspondantes des deux tableaux.
=unique(vstack('COMPTES À RECEVOIR'!A2:D,'PAIEMENTS COLLECTÉS'!A2:D),,true)
Le VSTACK concatène les deux tableaux verticalement. Le UNIQUE renvoie les lignes distinctes.
Ici, nous pouvons aussi utiliser la formule QUERY suivante.
=query(query(vstack('COMPTES À RECEVOIR'!A1:D,'PAIEMENTS COLLECTÉS'!A2:D),"Select Col1,Col2,Col3,Col4, Count(Col1) where Col1<>'' group by Col1,Col2,Col3,Col4"),"Select Col1,Col2,Col3,Col4 where Col5=1")
Comment cette formule QUERY compare-t-elle deux tableaux (ensembles de données) et supprime-t-elle les doublons ?
Avant d’utiliser cette formule, il est bon de comprendre sa logique. Il s’agit d’une formule QUERY imbriquée. La formule QUERY interne est la principale. Que fait-elle ?
=query(vstack('COMPTES À RECEVOIR'!A1:D,'PAIEMENTS COLLECTÉS'!A2:D),"Select Col1,Col2,Col3,Col4, Count(Col1) where Col1<>'' group by Col1,Col2,Col3,Col4")
Elle regroupe toutes les colonnes du tableau et renvoie le nombre d’enregistrements comme suit.
La requête externe filtre toutes les lignes qui contiennent « >1 » dans la dernière colonne.
Numéro de facture correspondant (méthode A) :
Supposons que le montant de la facture soit différent pour II000025 dans les deux tableaux. Les formules ci-dessus ne supprimeront pas lesdites factures car elles sont distinctes.
Dans ce cas, nous pouvons modifier notre formule QUERY précédente en ne regroupant que la colonne de la facture.
=query(query(vstack('COMPTES À RECEVOIR'!A1:D,'PAIEMENTS COLLECTÉS'!A2:D),"Select Col1,max(Col2),max(Col3),max(Col4), Count(Col1) where Col1<>'' group by Col1"),"Select Col1,Col2,Col3,Col4 where Col5=1")
2. Garder la première occurrence
Ici aussi, nous pouvons utiliser deux types de formules : Considerer uniquement le numéro de facture pour la correspondance (méthode A) ou la ligne totale (méthode B).
Ligne totale correspondante (méthode B) :
=let(ftr,unique(vstack('COMPTES À RECEVOIR'!A2:D,'PAIEMENTS COLLECTÉS'!A2:D)),filter(ftr,choosecols(ftr,1)<>""))Ici, Unique fait le travail. Le but de LET est de filtrer les lignes vides dans le résultat.
Numéro de facture correspondant (méthode A) :
=let(ftr,sortn(vstack('COMPTES À RECEVOIR'!A2:D,'PAIEMENTS COLLECTÉS'!A2:D),9^9,2,1,1),filter(ftr,choosecols(ftr,1)<>""))Ici, SORTN remplace Unique.
Le nombre en surbrillance dans la formule représente la colonne pour la correspondance. Ici, c’est la colonne de la facture, donc # 1.
C’est tout ce qu’il y a à savoir sur la comparaison de deux tableaux et la suppression des doublons dans Google Sheets.
Si vous souhaitez en savoir plus sur les fonctionnalités de Google Sheets, rendez-vous sur Crawlan.com pour découvrir des astuces supplémentaires.
Maintenant, vous pouvez facilement comparer des tableaux et supprimer les doublons dans Google Sheets. Amusez-vous bien !
