Register
Log In
Unanswered
Home
June 27, 2016
Offline
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?
See Answer
Dashboards
June 25, 2016
Offline
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
December 5, 2016
Offline
Pls show us the macro ? Tks!
Dashboards
June 25, 2016
Offline
Hi David
Unfortunately I am unable to show you the macro as it contains sensitive information.
December 5, 2016
Offline
It' OK Sunny.. we understand that..
June 27, 2016
Offline
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!)
Dashboards
June 25, 2016
Offline
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
June 27, 2016
Offline
Thanks for this Sunny.
No magic answers here, but at least it tells me I've not been wasting too much of my time already. (I'd hoped there might have been some useful UDFs you were aware of.)
KR
TimC
Most Users Ever Online: 57
Currently Online: clark shao, Max Neumann
12 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
