Zusammenfassung
Erfahren Sie, wie Sie eine Datendatei durch das Ausfüllen mehrerer Spalten aufteilen, um eine Datei zu erstellen, die von MindBridge unterstützt wird. Dies ist ein häufiges Problem bei vielen Datenexporten aus Buchhaltungssystemen wie QuickBooks.
Diese Methode kann sogar verwendet werden, wenn nicht alle Spalten in der ersten Zeile der Gruppierung einen Wert haben, und um leere Kontonummern auszufüllen, wenn die Kontobeschreibung vorhanden ist, die Kontonummer jedoch nicht.
Lösung
Betrachten Sie den folgenden Auszug aus einer QuickBooks-Hauptbuchdatei:
Die Probleme
- Diese Datei ist nach Transaktionen gruppiert; Datum, Transaktionstyp, Nr. und Name sind nur in der ersten Zeile der Transaktionsgruppe vorhanden und müssen nach unten gezogen werden.
- Kontonummer ist nicht immer vorhanden (siehe z.B. Zeilen 8, 13, 17, 25), aber Konto ist es. Konto kann der Kontonummer zugeordnet werden, aber Kontonummer kann nicht zugeordnet werden, es sei denn, die Leerstellen werden ausgefüllt.
- Es gibt unnötige Transaktionszwischensummenzeilen (9, 14, 19, 26)
Die Lösung (Formatierung erforderlich)
- Ziehen Sie Datum, Transaktionstyp, Nr. und Name in jede Transaktionszeile nach unten
- Füllen Sie die leeren Kontonummern mit dem Wert in Konto aus
- Schließen Sie überflüssige Gesamt-/Zwischensummen-/Leerzeilen (9, 14, 19, 26) und überflüssige Zeilen (Zeilen 1-4) über der Kopfzeile (Zeile 5) aus
Wir werden die überarbeiteten Spalten am Ende der Datei platzieren.
- Datum und Transaktionstyp sind immer in der ersten Zeile der Transaktionsgruppe vorhanden, aber Nr. und Name sind manchmal für eine Gruppe leer. Siehe die 3. und 4. Transaktionsgruppen, die in den Zeilen 16 und 21 beginnen.
- Unsere Logik wird sein, den Wert aus der ursprünglichen Spalte zu nehmen, wenn wir in der ersten Zeile der Transaktion sind, oder den vorherigen Wert, den wir gefunden haben, wenn wir nicht in der ersten Zeile der Transaktion sind.
Überarbeitetes Datum: Zelle L6 und darunter
=IF(LEN($B6)>0,IF(LEN(B6)>0,B6,""),L5)
-
LEN($B6)>0
testet, ob in Spalte B ein Wert vorhanden ist, indem getestet wird, ob die Länge größer als 0 ist. Da, wie wir beobachtet haben, das Datum immer in der ersten Zeile der Transaktionsgruppe vorhanden ist, dient dies als Überprüfung, ob wir in der ersten Zeile der Transaktion sind. Wir setzen $ vor das B, um das Kopieren der Formel in die restlichen Spalten zu erleichtern, um nach unten zu ziehen (Transaktionstyp, Nr., Name), da wir immer das Vorhandensein des Datums überprüfen werden, um zu überprüfen, ob wir in der ersten Zeile der Transaktion sind. -
IF(LEN(B6)>0,B6,"")
gibt das Datum zurück, wenn es vorhanden ist, "" (leere Zeichenfolge), wenn es nicht vorhanden ist. Dies kann für die Datumsspalte einfach auf B6 vereinfacht werden, da das Datum immer in der ersten Zeile der Transaktionsgruppe vorhanden ist, aber diese Logik wird verwendet, um die gleichen Formeln für Nr. und Name kopieren und einfügen zu können, die, wie oben erwähnt, nicht immer vorhanden sind. Da Excel 0 anzeigt, anstatt leer, wenn auf eine leere Zelle verwiesen wird, müssen wir ihm sagen, dass es "" zurückgeben soll, damit es eine leere Zelle anzeigt.
Hinweis: Die obigen Formeln, die auf leere Zeichenfolgen prüfen, gehen davon aus, dass die Zellen, die leer aussehen, in Excel tatsächlich leer sind. Wenn diese leer aussehenden Zellen tatsächlich Leerzeichen, geschützte Leerzeichen oder Phantomdaten enthalten, ersetzen Sie das Folgende für alle Instanzen von B6 innerhalb der LEN()-Funktion:
TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," ")))
- Das TRIM() entfernt Leerzeichen
- CHAR(160) gibt das geschützte Leerzeichenzeichen an, und die SUBSTITUTE()-Formel darum herum entfernt die geschützten Leerzeichenzeichen (Hinweis: auf einem Mac verwenden Sie CHAR(202) anstelle von CHAR(160))
Die endgültige Formel wäre also:
=IF(LEN(TRIM(CLEAN(SUBSTITUTE($B6,CHAR(160)," "))))>0,IF(LEN(TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," "))))>0,B6,""),L5)
Überarbeiteter Transaktionstyp, Nr., Name
Kopieren und einfügen und die Formel für das überarbeitete Datum nach unten ziehen.
Die Formeln in den Zellen M6, N6 und O6 sollten wie folgt lauten:
=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)
Überarbeitete Kontonummer
Unsere Logik, um die Leerstellen für die Kontonummer in Spalte P auszufüllen, wird sein, die ursprüngliche Kontonummer aus Spalte H zu nehmen, wenn sie vorhanden ist (Länge ist größer als 0), Konto aus Spalte I, wenn sie nicht vorhanden ist (Länge ist nicht größer als 0):
=IF(LEN(H6)>0,H6,I6)
Filter, um nur Transaktionszeilen beizubehalten
Wir beobachten, dass jede Transaktionszeile in der ursprünglichen Datei ein Konto hat. Also mit eingeschalteten Filtern, filtern Sie Leerstellen aus Spalte I (Konto) heraus:
Kopieren und Einfügen von Werten in eine neue Arbeitsmappe
Wählen Sie dann die Kopfzeile und die ausgewählten Transaktionszeilen (d.h. Zeile 5 und folgende in der gefilterten Ansicht - Sie können bei Zelle B5 beginnen, Strg-Umschalt-Ende drücken, um zum Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), dann kopieren, dann spezielle Werte in eine neue Arbeitsmappe einfügen.
Haben Sie noch etwas auf dem Herzen? Chatten Sie mit uns oder senden Sie eine Anfrage für weitere Unterstützung.