Hello!
I know the very basics of Excel. Even so, I know Excel is very capable of doing so many things so I hope there is a solution to my issue.
I have many pages of client data - names, addresses, DL#s that needs to be entered into an Excel database. The current layout of the data on
the paper copies is LN, FM (space, tab or invisible column) Address - physical address in top, city, state, zip below (space, tab or invisible column)
Lic#.
When I scan the sheets to my computer, they are saved as PDF. After I edit out the excess lines and info I don't need, I then export the data into Excel.
I'm good there. But when I open Excel to look at my spreadsheet, the data is not clean. Some cells have several rows of data merged into one rather than
having a row for each name, address & dl#. First, I un-merge all cells which scatters the addresses and DL#s into different columns. Once I get each data set
into its corresponding column (ex: all address lined up) I know I can use Text to Columns to spread out the data then use Copy+Paste Transpose to move it all back.
That takes a while, almost as long as typing out each client.
Is there a formula or macro or other way of changing all of the information so that a name, address and DL# are all on the same row but in separate columns?
The columns are separated into Last Name - First Name - Address - city - State - zip - DL#.
If you know of a better way, I would greatly appreciate it. Currently, it takes me over 2 hours to export then clean 75 names. The total project has about 20,000 names.
There HAS TO BE an easier way so I'm turning to the Excel Pro's for ideas. I appreciate any help that is offered.
Uploaded images - excel1 and excel2 are how the PDFs look. Excel data is how the data is exported.
I know the very basics of Excel. Even so, I know Excel is very capable of doing so many things so I hope there is a solution to my issue.
I have many pages of client data - names, addresses, DL#s that needs to be entered into an Excel database. The current layout of the data on
the paper copies is LN, FM (space, tab or invisible column) Address - physical address in top, city, state, zip below (space, tab or invisible column)
Lic#.
When I scan the sheets to my computer, they are saved as PDF. After I edit out the excess lines and info I don't need, I then export the data into Excel.
I'm good there. But when I open Excel to look at my spreadsheet, the data is not clean. Some cells have several rows of data merged into one rather than
having a row for each name, address & dl#. First, I un-merge all cells which scatters the addresses and DL#s into different columns. Once I get each data set
into its corresponding column (ex: all address lined up) I know I can use Text to Columns to spread out the data then use Copy+Paste Transpose to move it all back.
That takes a while, almost as long as typing out each client.
Is there a formula or macro or other way of changing all of the information so that a name, address and DL# are all on the same row but in separate columns?
The columns are separated into Last Name - First Name - Address - city - State - zip - DL#.
If you know of a better way, I would greatly appreciate it. Currently, it takes me over 2 hours to export then clean 75 names. The total project has about 20,000 names.
There HAS TO BE an easier way so I'm turning to the Excel Pro's for ideas. I appreciate any help that is offered.
Uploaded images - excel1 and excel2 are how the PDFs look. Excel data is how the data is exported.
