Rows out of order after PDF conversion happen when the converter reads pages, columns or text blocks in a different sequence from the printed page. Number every row by page and line, sort on those two columns to restore the printed order, then check the running balance row by row to prove each line is back.
Why do rows come out of order after PDF conversion?
Rows come out of order because a PDF need not store its text in reading order. The file says where each piece of text is drawn, and the software that made it may have written those pieces in any sequence. A converter that follows that stored sequence can return lines jumbled.
The usual causes:
- Stored order differs from visual order. A statement generator may write the footer first, then the table, then the header, or write each column of the table separately. Copying text or converting in stored order brings that sequence with it.
- Pages assembled from parts. A statement built by joining separate files, or one where a bank inserted a notice page, can have its pages in a different order inside the file from the one you see when scrolling.
- Multi-column layouts. Some statements print transactions in two columns side by side. A converter reading straight across the page mixes lines from the left and right columns.
- Rows split by line breaks. A description that wraps onto two lines can become two rows, and if the second line is read later, it lands away from its transaction.
- Sorting during cleanup. The cause is sometimes the person, not the PDF. A sort on description or amount, done to tidy the sheet, removes the printed order, and nothing in the sheet records what it was.
For a fuller picture of the stored-order problem in the PDF itself, see why PDF reading order goes wrong. The rest of this post deals with the sheet once the rows are already in Excel.
A row number column before any sort of converted rows
A row number column records the order the rows arrived in, so you can return to it after any sort. Add it before you touch anything else. It costs one column and saves rebuilding the order by hand from the PDF.
To add one:
- Insert a column at the left and head it Line.
- In the first data row, type 1. In the next, type 2.
- Select both cells and double-click the fill handle to number down the whole sheet.
Typed numbers are better than =ROW() here. A ROW formula renumbers itself after every sort, so it records the new order instead of the old one. If you used a formula, copy the column and paste it back as values.
Add a second column, headed Page, when the sheet holds several pages. Some converters put each page on its own sheet; in that case type the page number next to each block before you stack the sheets, or the information is gone once they are combined.
When the arrival order is already wrong
A line column records whatever order the converter produced, which may itself be wrong. It still helps, because the scrambling usually happens in blocks: whole pages, or whole column halves. The line numbers inside each block are right, and you only need to move the blocks.
Can you sort converted rows by date alone?
Sorting by date restores most of the order, but not all of it. A statement prints several transactions on most days, and a date sort leaves those same-day lines in whatever order they had before. If that order was wrong, it stays wrong, and the running balance column no longer steps from one line to the next.
A date sort has nothing to go on for rows with the same date, so at best it keeps their current order. It cannot recover the order the bank printed. That printed order matters for two reasons:
- The running balance. Each printed balance follows the line above it. With same-day lines swapped, the balances on those lines look wrong even though the totals are right.
- Matching to the PDF. When a client or auditor asks about a line, you want to point to it in the same position on the statement.
Sort by date first, then by Line, in one sort: Data, Sort, add Date as the first level and Line as the second. Same-day lines then follow the arrival order, which is right whenever each page arrived intact. Dates must be real dates for this; text dates sort as text, so "10/02" lands before "9/30". Sorting transactions by date in Excel covers converting them.
Restoring page order with a page number column
When whole pages are out of place, a page column fixes them in one sort. Read the page number printed on the statement, enter it next to every row from that page, and sort by Page and then by Line. Rows from page 2 follow page 1 again, and the order inside each page is kept.
To fill the Page column quickly, find the first row of each page, type its page number, and leave the rest of the block empty. Then select the column, use Go To Special, Blanks, type = and the cell above, and press Ctrl+Enter. Each blank takes the page number from the row above it. Paste the column as values afterwards.
Page headers help here. If the converter kept a repeated header such as "Page 3 of 6", use those rows to find where each page starts, fill the Page column, and only then delete the header rows.
When page numbers are missing
Some statements print no page numbers. Use the dates and balances instead. The last balance on one page equals the balance brought forward at the top of the next page. Chain the pages by matching those balances, and number them in that order.
Two-column statement layouts and interleaved rows
A two-column layout prints transactions in a left column and a right column on the same page. A converter that reads across the page produces interleaved rows: one line from the left, one from the right, and so on down the page. Neither date order nor line order is right, because the arrival order alternates.
Interleaving has a clear signature. The dates zigzag, for example 03/01, 03/16, 03/02, 03/17, and the balance column jumps back and forth between two sequences.
To separate them:
- Add a column headed Side. Mark odd lines as L and even lines as R with
=IF(ISODD(A2),"L","R"), where A is the Line column. Check a few rows against the PDF to confirm the pattern really alternates. - Sort by Page, then Side, then Line. All left-column rows come first, followed by all right-column rows, each in their own order.
- Check the balances. If the left column of a page ends on the balance that the right column starts from, the order is right.
Not every two-column page alternates neatly. A line that wraps in one column breaks the rhythm from that point. Wrapped lines also bring stray line breaks inside cells; removing line breaks from Excel cells cleans those before you sort.
Debits and credits in separate blocks
A different layout lists all deposits, then all withdrawals, each in date order. That is not a conversion error. It is how the statement was printed. If you want one list in date order, combine the two blocks and sort by date. Combining debit and credit columns into one amount column gives you a signed amount to check the balance with afterwards.
Checking restored row order against the running balance
The running balance proves the order. Each printed balance should equal the balance on the row above plus the amount on this row. When every row passes, the rows are in the order the bank printed them. A row that fails is out of place, or has the wrong sign, or has a neighbour missing.
The test works only with real numbers, so check that amounts and balances sum before you start. Making converted amounts editable numbers covers the fix if they are stored as text.
A worked example
The table below is an example of restored rows with a continuity check. The values are made up for illustration. Column G holds =ROUND(F1 + E2 - F2, 2), with the opening balance in F1.
| Line | Page | Date | Description | E (Amount) | F (Balance) | G (Check) |
|---|---|---|---|---|---|---|
| 17 | 2 | 06/08 | Rent payment | -1,450.00 | 2,310.40 | 0 |
| 18 | 2 | 06/08 | Card purchase | -36.15 | 2,274.25 | 0 |
| 20 | 2 | 06/09 | Gas station | -52.00 | 2,183.75 | 38.50 |
| 19 | 2 | 06/09 | Parking garage | -38.50 | 2,235.75 | -90.50 |
| 21 | 2 | 06/10 | Deposit | 900.00 | 3,083.75 | 52.00 |
Line 20 sits above line 19, and the check column flags it with a non-zero value. The two rows below it fail too, because each one is compared with the wrong neighbour. Swap lines 19 and 20 by sorting on Line, and the check reads 0 on every row. The size of the first non-zero value is a clue: here it equals the amount of the line that should have come first.
A statement with no per-line balance cannot be checked this way. Use the page totals or the statement summary instead, and compare dates with the PDF for the same-day lines.
FastBankConvert reads the statement layout and is built to give you dates, descriptions, amounts and balances in their own columns, ready for this check. It is getting ready to open; ask for trial access at Get started.