How to clean repeated headers and wrapped bank statement rows
A repeated column heading and a wrapped merchant description can both appear as extra rows after PDF table extraction. The first usually belongs outside the transaction list. The second may contain part of a real transaction and should be joined to its parent row after checking the PDF. Work in source order, tag each questionable line, and postpone deletion until you have compared counts and amounts. This guide walks through a fictional two-page statement where both issues happen at the same page break.
In this guide
Identify what each printed line contributes
Start by reading a complete page in the original statement. Locate the column headings, transaction dates, amount fields, running balances, page totals and any reference numbers beneath individual descriptions. Then compare the same area in the spreadsheet. A printed transaction is a logical record. A PDF can draw its description across two physical lines, repeat the header on every page, or place details in a second indented line. A spreadsheet converter has to infer relationships from the positions of those text fragments.
Create a working table with the original extracted fields, Source position, Source page, Row type, Parent position and Review note. Give every extracted line a position before sorting. You can use simple row types such as transaction, continuation, header, checkpoint and unknown. These names describe decisions you can explain. If the original page does not provide enough evidence to classify a line, retain the unknown label until you locate the adjacent material or a matching reference elsewhere in the transaction detail.
Read across the row before judging a blank date
A blank date can indicate a continuation, but some banks print the date once for a whole group of same-day transactions. A blank-date row containing a separate amount and a changed running balance may be a new transaction on the same day. A blank-date row containing just an indented merchant reference may belong to the preceding transaction. Treat the combination of layout, amount, printed balance and position as evidence. A blanket rule that deletes every blank date can remove valid transactions without leaving an obvious trace.
Recognize headers using more than one cell
A typical repeated header might contain “Date,” “Description,” “Money out,” “Money in” and “Balance.” Look for several of these labels in the matching fields on the same row. For example, Date appearing in the date field and Balance appearing in the balance field together make a stronger candidate than simply searching the full row for the word “balance.” A merchant description such as “Balance Fitness monthly membership” is legitimate transaction text. A broad keyword filter can remove a real purchase.
Keep a list of candidate header positions and inspect the first occurrence on each page. Sometimes the first heading row is followed by a second row containing units, a currency code, or a multi-line label. Tag those lines only after seeing how the source page groups them. If a bank changes the heading halfway through a statement, for example when moving from purchases to fees, read the new section headings carefully. The new section may use different columns or represent a separate account.
Distinguish carried-forward balances from transactions
A line labeled “Brought forward” or “Carried forward” often repeats the previous page's closing balance. It can validate page continuity, but adding it to the transaction amount column would invent a new movement. Compare that balance with the last printed balance on the earlier page and tag it as a checkpoint when they agree. The same words can occur inside a longer narrative explanation, so inspect their column position and amount placement before automatically excluding the line.
Join a wrapped description to the right parent
Consider a purchase printed on page one as “Harbor Office Supplies,” followed by an indented line reading “Invoice HOS-426 blue folders.” The first line shows a date, a payment of 43.80 and a closing balance. The second has no transaction amount or balance. Link its source position to the position of the purchase. In the cleaned transaction, combine the two strings with a single space or a clear separator. Preserve the original physical lines in the raw sheet so the join can be traced.
If more than one continuation follows, append the text in its printed order. Do not resort by merchant name first. A continuation describing an invoice number can easily attach to another purchase from the same supplier when multiple purchases share an amount. The safest parent identifier is a stable source position linked to the original page, with the extracted description used as supporting evidence. When two candidates remain plausible, leave the continuation unresolved and quote the relevant PDF line in a review note.
Handle a continuation split across a page break
Suppose page one ends with the main line of a payment and page two begins with the repeated headings, then the remainder of the merchant reference. A simple “attach to previous spreadsheet row” rule would attach the reference to the headings. Find the most recent confirmed parent transaction, stepping over verified page metadata. Confirm that the reference visually continues that transaction and that no intervening new amount or section appears. Use the parent position to document the cross-page relationship rather than relying on a calculated row offset.
Clean a two-page sample by hand
Imagine the extracted lines are an opening balance of 500.00, a purchase of 43.80, its separate invoice reference, a carried-forward balance of 456.20, the page-two column headings, a brought-forward balance of 456.20, and a refund of 10.00. Your raw table has seven lines. Your final transaction table has two records. The invoice reference is attached to the purchase. The carried and brought amounts are retained as checks. The opening balance remains a starting control rather than a movement.
Check the arithmetic before and after restructuring. The purchase reduces the balance to 456.20 and the refund raises it to 466.20. The outgoing total remains 43.80 and the incoming total remains 10.00. Joining the description should never change either number. If the final transaction count is three, look for a checkpoint that accidentally survived. If it is one, determine whether the refund was removed because its date field was blank. The row inventory explains discrepancies that an arithmetic check alone cannot locate.
Keep the cleaned text readable without changing the numbers
Long references can include multiple spaces used for visual alignment. Before applying any global replacement, examine meaningful strings such as account suffixes, confirmation codes or descriptions containing nonbreaking spaces. For a merchant narrative, collapsing repeated ordinary spaces may improve readability. For a reference identifier, removing a hyphen or leading zero may alter its meaning. Put reference codes in a separate text column when they have a consistent location. Preserve source text if you cannot establish which formatting is significant.
Decide what to automate on the next statement
A repeatable template can classify familiar header patterns and flag potential continuations. Begin with narrow rules that have a clear explanation. A row matching several known header labels can receive a proposed header tag. A line with blank amount fields and indented narrative can receive a proposed continuation tag. Inspect exceptions and page boundaries before dropping anything. Keep total row counts by proposed class and compare them to the first manually checked month to identify sudden layout changes.
If you use Power Query, set source fields such as invoice identifiers to text before automatic conversions remove leading zeros. Review the type-changing steps and select numeric types deliberately for confirmed amount columns. Microsoft explains the available controls in its guide to changing data types in Power Query. A data type can prevent an identifier from being misread, but it cannot establish which transaction owns a line of narrative. Parent assignment still depends on the statement's layout and context.
Check the difficult rows before deleting anything
Inspect the earliest and latest transactions, the first and last lines around every page break, and descriptions that span more than one continuation. Read merchant references in the cleaned table against the source and verify the original debit or credit amount remains beside the right merchant. A misattached description can preserve every numeric balance while assigning a payment to the wrong supplier. Include at least one text comparison in your review even when all arithmetic tests pass.
Export a transaction table with a clear trail
Keep a separate raw extract and final cleaned table. The final table can omit header and checkpoint rows once they have served their purpose. Store the source positions associated with each cleaned record, especially when one transaction absorbed several physical lines. Include a small summary showing how many headings, continuations, checkpoints and unknowns appeared. This helps a reviewer understand why the final transaction count is smaller than the number of extracted spreadsheet lines and where any exceptions still need attention.
For a fresh digital statement, start with the bank statement to Excel tool. Its review screen lets you edit a cell and remove an unwanted row. Copy continuation text into the correct description before removing its old row, and keep the original PDF available for verification. For a missing transaction or a balance that changes at the first row of a new page, consult the statement reconciliation guide. Treat every page boundary as a place to confirm both the text relationship and the money movement.
What if a reference has its own amount?
An installment breakdown, tax detail or currency conversion note can include a number that resembles a second payment. Look for a corresponding separate balance change before classifying it as another transaction. If the parent payment already accounts for the whole movement, retain the detail as supporting information and label its amount field according to the printed meaning. When the source is unclear, flag the pair for review. A second numeric value alone does not establish that the account experienced a second independent movement.