Hello, I'm hoping someone can help with this work project. I have experience with formulas and pivot table and am somewhat familiar with Power Query but apparently not enough for it to help me with this work project.
I've been trying to find an Excel work-around for this project to simplify it so it doesn't involve days (maybe even weeks) of unnecessary data entry. I've used Power Query successfully a number of times, but I am completely stumped/stuck by this particular issue. And it's driving me crazy because I know there has got to be a way to do this that I simply cannot find searching the web or YouTube. Been trying to find a solution for 5 days now, and I'm about to give up and start the painful data entry process.
What I'm trying to accomplish is exporting text from a PDF and importing it into Excel place the text into specific columns so it has some structure and can be sorted, filtered, and have pivot tables created with it.
The text was originally entered/created in a 3-column format that a PDF was eventually created from. It pertain to design awards that my company has received over the years dating back to 1954. Problem with this PDF is that none of the categories are labeled (Award, Year, Organization, Project Name, City, State, etc.) to make exporting the data (then performing some clean up) to successfully import it into Excel placing it into the appropriate columns, and then there is the issue that not every award contains all of the fields I mentioned. Some has a few of the field labels and some have all of them (sometimes multiple times). And then there is the 3-column format issue. It's just a mess.
I've attached that PDF that shows two (2) different examples (on Page 1 & 3) of how the text is laid and an Excel file showing how we eventually need the data imported, formatted, and structured into Excel. I entered the data/text from the two (2) PDF examples into the Excel file so one can see how we need it laid out.
My brain is fried, and I'm completely lost at this point. I've tried every single thing I can think of and many things I didn't even know how to do until watching endless number of YouTube videos. If there is anyone in this forum who has expertise and/or a clue how to accomplish, I would be forever grateful.
Thanks in advance for any help you can provide!
Pascal
