Converting Multi-Row Multi-Field Records to Workable Columns

[Nicole_Michelle01]

Hi, I have a file with approximately 500 records with the same fields listed in rows which I need to convert to columns so that I can sort, etc. on the total list of records. There are fields which I don't need and there are multiple fields in some of the rows.

Can I post a sample of the file and an example of what I want the final output to look like?

Let me know if you can help, I couldn't find anything similar in the archives.

Thanks!

~ Nici

 

[Richard Schollar]

Nici

If you navigate to the very top of the forum webpage, you should see one of the link options saying you have a new message - my email address is in there (both work and home - I won't be at work much longer today, so home email is best should you want a response before Monday ).

Richard

PS: If the fields are all exactly 160 characters long, it really should be straightforward to pull the data out.

 

[javajoe]

If I understand you correctly, you could probably temporarily add two new columns as a way to get rid of the bogus headers mixed in with your data. In column 1, just number them down from 1-500, so you can get the proper sequence back using an A>Z sort.

Then, in the second column, write a formula that places an "X" in the field after every nth record (you could also use an advanced filter with Contains...). Temporarily sort on that column, delete all the rows with X's, and then use your first column to sort the data back to the proper sequence. Then delete those two tempoary columns.

You will now have all of the bogus "headers" removed from your dataset. You can then use TEXT TO COLUMNS and/or the Copy/Paste Special Transpose feature to move the remaining data into columns.

Hope that helps!

 

[Nicole_Michelle01]

Thanks for all your help Richard and JavaJoe. I took a bunch of different macros from other solutions posted to complete the scrub. They all work beautifully, you guys are great!