Sage 300 reconcilliation

Admin — Tue, 21 Jul 2026 14:58:20 GMT

Page créée avec « Create excel File vignette|Reconcilliation1 # Common Services > Bank Services > Bank Transactions # Reconcile Statements # File > Export # Type: Excel 2007 > Browse (Name file0 # Ok Copy excel file to your computer. vignette|Reconcilliation2 # Open the file # Make sure to select the first tab on the bottom (by default both are selected) # Select CTRL+A to select all the cells # Go to Data > Sort... »

Nouvelle page

Create excel File
[[Fichier:Reconcilliation1.png|vignette|Reconcilliation1]]

# Common Services > Bank Services > Bank Transactions
# Reconcile Statements
# File > Export
# Type: Excel 2007 > Browse (Name file0
# Ok

Copy excel file to your computer.
[[Fichier:Reconcilliation2.png|vignette|Reconcilliation2]]

# Open the file
# Make sure to select the first tab on the bottom (by default both are selected)
# Select CTRL+A to select all the cells
# Go to Data > Sort
# Select sort by TRANSDATE (make sure my data has headers is clicked)
# Press ok

Format Data

# Go down to the appropriate line to delete older data(for the date you're looking at) ex. last line of 2026-05-27 (for June reconciliation).
# Select the line by clicking on its number on the left.
# Use CTRL+SHIFT+UP to select all the lines above (move down one line with SHIFT+DOWN to keep the headers)
# Right click on one on the line numbers and select delete to delete all the irrelevant lines.
# Repeat the same with later lines (i.e. 2026-07-01 and newer for june) by using CTRL+SHIFT+DOWN to select newer lines and delete.
# Select column (Totclear) and use CTRL+SHIFT+RIGHT to select all the lines to the right, you can delete them as they wont be relevant. If you're doing current month, totclear can show previous month reconciliation and you can leave it.

Format Data (2), now we will improve readability.
[[Fichier:Formatting1.png|vignette]]
[[Fichier:Formatting2.png|vignette]]

# Make column I and J big enough to see the information.
# Select column D (srceapp), go to the home tab and select conditional formatting
# Highlight cell rules > Text that contains
# Use AP (value) with Light Red Fill and BK (value) with Green Fill
# Select column E (transtype), select conditional formatting
# Highlight cell rules > Equal to
# Use 1 (value for negative amount) with light red fill, this will differentiate if the amount is a negative (1, credit) or positive (2, debit)
# Repeat for column G (entrytype), value 5, light red fill (value 5 means the reversal of a transaction)

Rename Sheet and move to the main Book (File)
[[Fichier:Formatting3.png|vignette]]
[[Fichier:Formatting5.png|vignette]]

# Make sure to open the appropriate main file (in this case 2025-2026)
# Rename the sheet to the corresponding month (i.e. June)
# Right click the sheet and select move or copy.
# In the popup, select the appropriate book and click OK (make sure create a copy is deselected).
# In the appropriate book, use move or copy again on any colors tab and create a copy (make sure create a copy is selected) to create a corresponding colors_june tab.

Use the file

# In the colors tab, you can see the corresponding color codes to reconcile the payments.
# In the main tab, color the corresponding payments with corresponding colors and use Thick outside borders on cells that reconcile with the received amounts.

[[Fichier:Reconcile2.png|vignette]]