PDF2XL Mapping to Pre-Existing Worksheet Layout...

kcjones76

New Member
Joined
Apr 20, 2009
Messages
3
A LITTLE BACKGROUND INFO

We are currently converting mortgage statements that come to us as scanned PDFs. These statements come from over 100 municipalities across Canada. There are over 70 variations as the statements are created using different pieces of software. As a result, a unique PDF2XL page layout has to be created for each one.

Essentially a page layout properly identifies all the tables and fields of a scanned PDF document. Each statement is comprised of core information that needs to be interpreted as columns of a single table.

Roll No. | Owner | Address | Mortgage No. | Installments | and so on...

If the source PDF is well structured, it's not too bad, even though there are so many variations. There are also fields that reside outside the main table information, like dates, titles, and other information that would stand on its own.

Once a PDF2XL is established for a certain variation, we can apply that same layout to any scanned PDFs that follow that exact format. PDF2XL then allows us to convert the document to an Excel file. Each table, and/or field grouping can be converted to individual worksheets within an Excel workbook.

---
THE CHALLENGE

The 70+ variations have to map to only three (3) standard Excel layouts. We are trying to automate the process as much as possible because we have 50,000 records to process (with 5-20 per 8.5x11 PDF). Is it possible to populate a target Excel document with the converted data from PDF2XL?

We have limited control over how the converted tables and/or fields are positioned on a worksheet. Basically they start from A1. The three (3) standard Excel layouts however, have their corresponding columns starting further down on the worksheet, leaving space above for standalone field information.

It would be great if there was a way append the PDF2XL converted worksheet(s)/workbook to the one of the three (3) standard Excel layouts. At which point, lookups, dynamic ranges, etc could be established to automagically have that converted data from the source worksheet(s) flow into the main worksheet containing the standard layout.

Mortgage No. column starts on A1 - converted PDF2XL Excel worksheet
Mortgage No. column starts on B6 - standard Excel layout

Can I have it so the Mortgage No. column and it's underlying data flow into the standard Excel layout at B6?

---
I'M SCARED!

I only mentioned the lookups and dynamic ranges because it seems that that is the direction to go. That said, I don't even know where to start. I don't really delve into Excel and my formula expertise is limited to the SUM function... hehe

If anyone can help us, that would be amazing. If I can be directed to some more specific resources, that would also be great. There is just so much information out there, I don't know what to filter out and where to focus in on. For all I know, I could be way off base with my thinking...

Thanks in advance!

--
desperate to automate such a mundane time-consuming task,
kcjones76
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

kcjones76

New Member
Joined
Apr 20, 2009
Messages
3
I'm still hunting for some answers. I'm anxious to find a solution as we are live in the project. It was suddenly dropped on us and we're behind the eight ball on this one...

Thanks again!
 

kcjones76

New Member
Joined
Apr 20, 2009
Messages
3
error initially posting, but first one actually succeeded...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top