Split uneven data from a single columns to multiple columns

[pfoz]

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.

[JBeaucaire]

To parse data from a single column in to row-based data and have Excel do it automatically, you must be able to teach Excel the "logic" of the data in the single column. For instance, each record is always 10 cells and gets parsed out evenly into 10 columns. Or each cell has a specific piece of "key" data that is always present, like the cells with names have "Name:" at the beginning, or "Address:", etc. You get the idea.

Your data is completely random. Only a human eye will be able to apply context to data with no rhyme nor reason nor key in the data structure itself. In my opinion.

Now, having said that, any idea you can come up with that is consistent and true for each set of data and allows you to logically identify the data for each complete set and works for all data sets, I can certainly help convert your idea into a formula or a macro to perform that logic automatically for you.

[aydeegee]

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

[pfoz]

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.

[pfoz]

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

[aydeegee]

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

[JBeaucaire]

Splitting on the post codes only works if every "Set of data" has the post codes in the exact same place "within each group". Meaning if you spot a post code, the city and zip are always X rows above, the address is always XY rows above, the name is always XZ rows above, the business name is always XX rows above.

If you can design a set logic that when applied, properly points to each "piece" of data with every group having found the post code, then we can help you automate that discovered logic.

[pfoz]

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.

[pfoz]

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.

[aydeegee]

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)

Please Login or Register  to view this content.

. 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

[pfoz]

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.

[aydeegee]

PM sent

Regards
Alastair