Need to pull specific info from Excel Sheet (over 1 million rows)

Hello,

I have a PDF file that is over 16,000 pages with invoices. I converted the file to excel which pretty much put all the info into Column A, but separated into rows. I need to pull about 16,000 different loan numbers. The excel sheet listed "Loan #:" in a single cell, and the actual loan number in the cell above the label. (i.e. "cell A16=Loan #:" "cell A15=1234567") I would like to pull all loan numbers onto another workbook. (Then I would also like to pull invoice numbers, loan amounts, etc. These values are set up in a similar manner: labels are underneath the value. I am hoping to apply a similar formula as I would to pull the loan numbers.) Please note, all cells are in Column A. I've been boggling my mind with which formula to use and how.

Please help!

Each loan has a different amount of lines. The first ten invoices are about the same and then it changes from there. I first tried to use an INDIRECT formula, then an OFFSET formula but there isn't a steady pattern to follow.

I attached a PDF version with loan numbers changed and other personal information blacked out.

Thank you for your assistance.

Book1.pdf

Your initial description says that for each value, the label is in the row after the value. In your PDF file, there are several labels that do not have any data (acquisition date, referral date, payee code, invoice date). Are you interested in any of those fields, and if so, how do we read this? You have highlighted Date Submitted in yellow, plus the following line, which is a date. Is this an exception where the data comes after the label?

The PDF file gives an idea of what's going on but isn't sufficient, at least not for me. All those blackouts and highlights make it difficult to see what's going on and what you intend. In many cases there are multiple lines between labels. For blacked out lines I can't tell if you are blacking out data, labels, or both.

You said that the pattern changes after the first 10 invoices but your PDF file only has three invoices. What changes? That might be important for a robust solution.

Your initial description above has the dreaded "etc." Can you provide a specific list of which fields you want to extract?

It may be possible to do this with formulas, but to develop this we would need to have sample data in Excel, otherwise we would have to enter all your data by hand into Excel ourselves. I would need to see an actual Excel file with at least 2-3 loans, private data removed. The ideal situation would be another Excel file showing what you want the result to look like, but the list might be enough.

Sorry about the confusion. I only need to pull the information that is highlighted in yellow: Loan #, Invoice #, Invoice ID, Date Submitted & Amount. And yes, Date submitted is the only one where the data is underneath the label.

I attached an excel sheet this time. Sheet 1 shows what information I am trying to retrieve. And Sheet 2 has the first 3 loans.Book1.xlsx

Try this. Put your real data to replace your sample data, and then fill down the formulas on the other sheet as needed. I don't know where you get page # from. The last column "Next Location" is a "helper" column that is an intermediate result used in the other formulas.

I renamed your "Amount" column to "Invoice Level Exceptions," because that is how it is labeled in the data. I didn't know what Amount was but it looks like this is what is highlighted in your PDF file.

Assumptions are fairly minimal. They are:

The Loan # is the first field in each loan that is included in the extracted data
The labels for each extracted field are always exactly the same

THAT WORKED!! I've never been so happy to see a formula follow through on excel!

For some reason, after about 700 lines, I get an error of "#REF!" I haven't been able to figure out why so I've just been creating a new workbookafter 700 or so lines.

This is amazing though!! Thank you so much for your help! You literally saved me MONTHS (if I had to complete this manually).