Résumé
Apprenez à dissocier un fichier de données en remplissant plusieurs colonnes pour produire un fichier pris en charge par MindBridge. C'est un problème courant dans de nombreuses exportations de données provenant de systèmes de comptabilité tels que QuickBooks.
Cette méthode peut même être utilisée si toutes les colonnes n'ont pas de valeur sur la première rangée du groupe, et pour remplir les numéros de compte vides lorsque la description du compte est présente mais que le numéro de compte ne l'est pas.
Résolution
Considérez l'extrait suivant d'un fichier de grand livre général QuickBooks :
Les problèmes
- Ce fichier est regroupé par transaction; Date, Type de transaction, No., et Nom sont présents uniquement sur la première rangée du groupe de transactions et doivent être tirés vers le bas.
- Le numéro de compte n'est pas toujours présent (voir par exemple les rangées 8, 13, 17, 25), mais le compte l'est. Le compte peut être mappé à l'ID du compte, mais le numéro de compte ne peut pas l'être à moins que les blancs soient remplis.
- Il y a des rangées de sous-total de transaction inutiles (9, 14, 19, 26)
La solution (formatage nécessaire)
- Tirez Date, Type de transaction, No., et Nom vers le bas jusqu'à chaque rangée de transaction
- Remplissez les numéros de compte vides avec la valeur dans Compte
- Excluez les rangées de total/sous-total/blancs superflues (9, 14, 19, 26) et les rangées superflues (rangées 1-4) au-dessus de la rangée d'en-tête (rangée 5)
Nous mettrons les colonnes révisées à la fin du fichier.
- Date et Type de transaction sont toujours présents sur la première rangée du groupe de transactions, mais No. et Nom sont parfois vides pour un groupe. Voir les 3e et 4e groupes de transactions commençant aux rangées 16 et 21.
- Notre logique sera de prendre la valeur de la colonne d'origine si nous sommes sur la première rangée de la transaction, ou la valeur précédente trouvée si nous ne sommes pas sur la première rangée de la transaction.
Date révisée : Cellule L6 et en dessous
=IF(LEN($B6)>0,IF(LEN(B6)>0,B6,""),L5)
-
LEN($B6)>0
teste s'il y a une valeur dans la colonne B en testant si la longueur est supérieure à 0. Comme nous l'avons observé, Date est toujours présent sur la première rangée du groupe de transactions, cela sert de vérification si nous sommes sur la première rangée de la transaction. Nous mettons $ devant le B pour faciliter la copie de la formule aux autres colonnes à tirer vers le bas (Type de transaction, No., Nom), car nous vérifierons toujours la présence de Date pour vérifier si nous sommes sur la première rangée de la transaction -
IF(LEN(B6)>0,B6,"")
renvoie la date si elle est présente, "" (chaîne vide) si elle ne l'est pas. Cela peut être simplifié à simplement B6 pour la colonne Date car la Date est toujours présente sur la première rangée du groupe de transactions, mais cette logique est utilisée pour pouvoir copier et coller les mêmes formules pour No. et Nom, qui, comme mentionné ci-dessus, ne sont pas toujours présents. Parce qu'Excel affichera 0 au lieu de vide lorsqu'il fait référence à une cellule vide, nous devons lui dire de renvoyer "" pour qu'il affiche une cellule vide.
Remarque : Les formules ci-dessus vérifiant la chaîne vide supposent que les cellules qui semblent vides sont en effet vides dans Excel. Si ces cellules apparemment vides contiennent en fait des espaces, des espaces insécables ou des données fantômes, remplacez ce qui suit pour toutes les instances de B6 dans la fonction LEN() :
TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," ")))
- La fonction TRIM() supprime les espaces
- CHAR(160) indique le caractère d'espace insécable, et la formule SUBSTITUTE() autour de celui-ci supprime les caractères d'espace insécable (remarque : sur un Mac, utilisez CHAR(202) au lieu de CHAR(160))
Donc la formule finale serait :
=IF(LEN(TRIM(CLEAN(SUBSTITUTE($B6,CHAR(160)," "))))>0,IF(LEN(TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," "))))>0,B6,""),L5)
Type de transaction révisé, No., Nom
Copiez et collez et tirez vers le bas la formule trouvée pour la Date révisée.
Les formules dans les cellules M6, N6, et O6 devraient être les suivantes :
=IF(LEN($B6)>0,IF(LEN(C6)>0,C6,""),M5)
=IF(LEN($B6)>0,IF(LEN(D6)>0,D6,""),N5)
=IF(LEN($B6)>0,IF(LEN(F6)>0,F6,""),O5)
Numéro de compte révisé
Notre logique pour remplir les blancs pour le numéro de compte dans la colonne P sera de prendre le numéro de compte d'origine de la colonne H s'il est présent (longueur supérieure à 0), Compte de la colonne I s'il ne l'est pas (longueur non supérieure à 0) :
=IF(LEN(H6)>0,H6,I6)
Filtre pour ne garder que les rangées de transaction
Nous observons que chaque rangée de transaction dans le fichier d'origine a un compte. Donc avec les filtres activés, filtrez les blancs de la colonne I (Compte) :
Copiez et collez les valeurs dans un nouveau carnet de travail
Sélectionnez ensuite la rangée d'en-tête et les rangées de transaction sélectionnées (c'est-à-dire, la rangée 5 et au-delà dans la vue filtrée - vous pouvez commencer à la cellule B5, appuyer sur Ctrl-Maj-Fin pour atteindre la fin du fichier, et ajuster la position si nécessaire), puis copiez, puis collez les valeurs spéciales dans un nouveau carnet de travail.
Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir de l'aide supplémentaire.