Split uneven data from a single columns to multiple columns

Hi all,

I wonder if anyone can help with this complicated problem.

I have a large amount of data that is on A5 paper, I have scanned all the data (6,000 plus) and have them in a pdf document, I am able to then convert this pdf into excel using adobe online, but unfortunatley it converts all fields into a single column with 40,000 plus rows.

I also have an excel spreadsheet with 250,000 records, all formatted nicely in columns for name, address, postcode etc, what I need to do is check for duplicates within the two sets of data.

If I could change the converted data into columns with headings, then I am certain I can run some kind of lookup formula to complete the task, but with the data in a single column this is not going to be posible.

Through this forum I have found how to split single colums into multiple columns, but unfortuanely my converted data varies in length from record to record, and as part of the convertion it also brings in a few abnormalities like the barcodes.

In summary what I need to do is find a way of putting the data from a single column into multiple columns but by looking at what it is moving.

I have attached a snap shot of the data both in the raw form and converted (manually), (names and addresses have been chaged).

Thank You in anticipation.

Hi,

Thanks for your reply.

The only thing I can find that should be present in each record would be a postcode, but that will obviously change from record to record but should be of a similar formula, would this be enough or is this to vauge?

I have also experimented with converting the original data from pdf to word, then copy and pasting the word document into excel, this does space things out slightly better, but again doesn't give any definate patterns.

In reallity all I need to do is take the scanned sheets and mark them on the original data as all the records I have need to be removed from a spread sheet containing 250k plus records, I have approximately 15k sheets to mark. Last time we did this project we did it manually, but I was hoping to automate the process. Any other suggestion would be gratefully recieved, Perhaps a macro that searches through the converted pdf data and compares it with the original data, then adds a marker to show that it has been found, the best method for checking is via the postcode, then a secondary check on the rest of the address as sometimes the postcodes appear in more than 1 record.

It should be possible to get all the postcodes in one column by looking for the pattern "xxxx xxx" , "xxx xxx" or "xx xxx". Sort, and let the computer deal with matches on names etc., leaving the human eye to try and identify near-matches (mis-spellings, middle initials etc)

Regards
Alastair

Alastair,

Thank You for your reply.

Further to my other reply and following on from your suggestion, is it possible (and if so how?) to seperate into collums based on splitting at the postcodes? and would it matter whether the postcodes vary
i.e.
XX1 2XX
X1 2XX
XX12XX (no spaces)
X12XX (no spaces)

Thanks in advance.
Paul

Hi Paul

The "no spaces" did not come into my thoughts when I posted my reply, but yes, all of the above can be accommodated. I will get round to writing something at the weekend - bit busy with the day job at present.

Regards
Alastair

Alastair,

Very much appreciated, Thank You.

If you can think of a simpler way of matching data that is in excel / word / pdf format with another set of excel data then marking that data as a repeat without having to have both sets of data formatted identiclally then any suggestions greatly appreciated.

Unfortunately the position of the postcode does vary from record to record dependant on the number of lines used for the address, so it looks like that method may not be practicle on this occasion.

I am searching the net for other possibilities, there must be a way of the PC reading data from word / excel / pdf files and then looking at another excel file to see if there is a match and marking that match in some way.

Hi Paul

I attach a file that will put post codes into a single column.

In your post #5 you gave the various patterns that could be post codes. Unfortunately your sheet "Table Converted" includes (Row 3)

xx xxx [lots of spaces] [some numbers]

. As JBeaucaire said

you must be able to teach Excel the "logic" of the data

My routine will not cater for that. (but a routine could be written if there enough occurrences).

I have added a routine to put spaces in the postcodes

To see the codes press Alt and F8 and step into either routine

I also enclose a routine to compare datasets. Have a read of the instructions and see if might benefit you.

Regards
Alastair

Alastair,

Thank You for this.

I have trialed this with the live data, and the matching routine works well with a single collumn, although I can't get it to add further collumns to check (probably my fault).

Would you be interested in doing this task for me for a fee? if I sent you the data (400k records) and then converted data from pdf.

For data protection however, we would need to be in contact privately either via telephone or email.

Regards
Paul Foster.

PM sent

Regards
Alastair