Hello,
I found the answer to this question about 6 months ago, but now it is eluding me.
I'm trying to transpose ugly old data from an AS400 system into Excel. I don't want to put the actual information on a public forum, but a close approximation would look like this:
Student 1, Street 1
Student 1, City 1
Student 1, Phone 1
Student 1,
Student 2, Street 2
Student 2, Apartment 2
Student 2, City 2
Student 2, Phone 2
Student 3, Street 3
Student 3, City 3
Student 3, Phone 3
Student 4, Street 4
Student 4, City 4
Student 4, Phone 4
Student 4,
Student 5, Street 5
Student 5, City 5
Student 5,
Student 5, Phone 5
Student 5,
Student 5,
Student 5,
As you can see, the data is a mess. Usually, the data is grouped into 4 rows, but sometimes it's 3 rows and other times more. Sometimes the data skips a line between the city and the phone number. Sometimes, there is no city listed, just a street name and number. Sometimes there are 3 or more blank lines after a listing.
Column one is a student number, and column two is the various data types.
Because the data is in such disarray, I don't see how any solution will be perfect. But, if I can at least break out the rows into columns, I think that I can figure it out from there. a good solution would look like this:
Student 1, Street 1, City 1, Phone 1, *Blank
Student 2, Street 2, Apartment 2, City 2, Phone 2
Student 3, Street 3, City 3, Phone 3
Student 4, Street 4, City 4, Phone 4, *Blank
Student 5, Street 5, City 5, *Blank, Phone 5, *Blank, *Blank, *Blank
From there, I can at least get started. Any help is much appreciated. Thanks!
Bookmarks