Hello All! Thank you in advance for reading.
Long time browser - first time poster. I've always been able to find all the questions I needed just browsing the forums, but this time I seem to be at a bit of a loss. If something is out there and I've missed it I apologize.
About 10 years ago I took an excel class in college, and had something demonstrated during one of our classes that I'm trying now to figure out how to do. I do not remember if it was a macro, an add-in, or even a third party software. (My memory isn't what it used to be, I guess!)
I have a PDF document that we receive from a vendor every month. The vendor refuses to give us the information in any way other than the format we're getting it in now. The document can be hundreds of pages long, and everything is currently being hand-keyed by staff into our system. I have verified that the document can be cleanly OCR'd using Adobe full and then can be converted to a .txt document. I do not remember whether what I originally saw demoed was working fom a PDF or TXT document.
This document is separated by name, but each record can be anywhere from 4 to 40 lines of data. In the tool I saw being demo'd, the first and last lines of the record had to be uniquely formatted (which they are) and the tool used that information to determine the start and end of each of the records in the document.
After the beginning and ending were set, we were able to highlight certain data points (such as the employee name in the header or the subtotal in the final line) that could appear in the same cell on every row in excel that came from that larger record. The system would then pull each individual purchase or invoice underneath into excel assigning 1 row for each item. All of the superfluous data that wasn't specifically assigned by us as needing to come in by way of the import was left behind in the original document.
Theoretically, this would leave me with an excel document that could cleanly be imported into the software looking something like this:
John Smith Invoice 043 $17.00 Folders $84
John Smith Invoice 125 $26.00 Pens $84
John Smith Invoice 222 $20.00 Coffee $84
John Smith Invoice 843 $21.00 Software $84
I've got a high level of comfort with excel (regular user of pivot tables and most formulas including vlookups and the likes). I've got a very basic level of understanding of Macros but I'm definitely still learning where the coding comes in. I took a couple classes in college on coding visual basic, so I'm rusty but I have a bit of a foundation there.
My question here is 2-fold.
1 - Any idea what I saw all those years ago? Is there an add-in/macro/software out there that does this already and I'm just not finding it? Has anyone seen something like this? I can't imagine I'm the first person to ever want to streamline something like this.
2 - If there isn't anything out there and it was likely just something the professor had created, where is the best place to start creating this? Is this the type of thing I should be trying to do with a Macro or is it going to end up being too complex? Do I need to put time into learning a programming language to try and create an add-in for this? What would be a suggested starting point?
I have 2007 at work, but personally I have computers with 2010 and 2013 on them, so version isn't an issue for me. I have the full version of adobe as well.
Thanks again for pointing me in the right direction!
Long time browser - first time poster. I've always been able to find all the questions I needed just browsing the forums, but this time I seem to be at a bit of a loss. If something is out there and I've missed it I apologize.
About 10 years ago I took an excel class in college, and had something demonstrated during one of our classes that I'm trying now to figure out how to do. I do not remember if it was a macro, an add-in, or even a third party software. (My memory isn't what it used to be, I guess!)
I have a PDF document that we receive from a vendor every month. The vendor refuses to give us the information in any way other than the format we're getting it in now. The document can be hundreds of pages long, and everything is currently being hand-keyed by staff into our system. I have verified that the document can be cleanly OCR'd using Adobe full and then can be converted to a .txt document. I do not remember whether what I originally saw demoed was working fom a PDF or TXT document.
This document is separated by name, but each record can be anywhere from 4 to 40 lines of data. In the tool I saw being demo'd, the first and last lines of the record had to be uniquely formatted (which they are) and the tool used that information to determine the start and end of each of the records in the document.
After the beginning and ending were set, we were able to highlight certain data points (such as the employee name in the header or the subtotal in the final line) that could appear in the same cell on every row in excel that came from that larger record. The system would then pull each individual purchase or invoice underneath into excel assigning 1 row for each item. All of the superfluous data that wasn't specifically assigned by us as needing to come in by way of the import was left behind in the original document.
Theoretically, this would leave me with an excel document that could cleanly be imported into the software looking something like this:
John Smith Invoice 043 $17.00 Folders $84
John Smith Invoice 125 $26.00 Pens $84
John Smith Invoice 222 $20.00 Coffee $84
John Smith Invoice 843 $21.00 Software $84
I've got a high level of comfort with excel (regular user of pivot tables and most formulas including vlookups and the likes). I've got a very basic level of understanding of Macros but I'm definitely still learning where the coding comes in. I took a couple classes in college on coding visual basic, so I'm rusty but I have a bit of a foundation there.
My question here is 2-fold.
1 - Any idea what I saw all those years ago? Is there an add-in/macro/software out there that does this already and I'm just not finding it? Has anyone seen something like this? I can't imagine I'm the first person to ever want to streamline something like this.
2 - If there isn't anything out there and it was likely just something the professor had created, where is the best place to start creating this? Is this the type of thing I should be trying to do with a Macro or is it going to end up being too complex? Do I need to put time into learning a programming language to try and create an add-in for this? What would be a suggested starting point?
I have 2007 at work, but personally I have computers with 2010 and 2013 on them, so version isn't an issue for me. I have the full version of adobe as well.
Thanks again for pointing me in the right direction!
