June 27, 2016
Not sure whether this is strictly Excel, but I frequently need to copy data from a PDF file, but when the data is copied the formatting is lost, i.e. the rows aren't always in the same row, and likewise the columns. Do you have any cost-free tips/advice as to how I can remedy this? What would you do?
VIP
Trusted Members
June 25, 2016
Hi Tim
Form my own experience, I would normally use a macro to "clean" the copied data.
I have helped a colleague who used to take hours to "clean" the data on a weekly basis. It was error-prone and not productive.
The macro only took a few seconds to do the job.
If you need to do this frequently, it would be worth the while to consider using a macro.
Sunny
VIP
Trusted Members
June 25, 2016
June 27, 2016
Thanks, Sunny.
Could you say, generally speaking, what does the macro include? I have to do this with PDFs from various sources, so obviously one macro wouldn't fit every situation. Is it merely sorting rows (or columns) and moving data from one cell to another, or does it include something more sophisticated that would make me say "Wow! That's good!" (Many answers do!)
VIP
Trusted Members
June 25, 2016
Hi Tim
You are right about having many macros as the data is different for each situation. For most people, completing their job in only a few seconds (instead of hours) is guaranteed to create a WOW effect.
Most of the time the macro is not fantastic (I have to hard-code many lines) but it does contain some custom functions and get the job done.
Generally the macro does the following (not necessary in the following order):
- Delete all unwanted rows (page number,headers,address etc)
- Add custom headers
- Extract some data from the middle of a specific row after a certain text
- Concatenate text
- Break some rows into different columns and formatting them
- Adding grand totals to the output
- Generate a Pivot Table (if required)
Sunny
Answers Post
1 Guest(s)