Convert column into multiple columns with different criteria

Hello fellow excel'ers,

Here's my situation:

Column A has over 10,000 rows. Here is an example of how the column is structured.


"Column A"

Birmingham - HooverGA - Atlanta 30341*
Atlanta - Sandy Springs - Marietta
2019 Audi A5 Sportback*
(Car)
dealer to dealer - ask for Brit 774-855-6046
Order ID:*BM26253(No Carrier Assigned)
158 mi*($1.27/mi)
2/24/2020
2/24/2020
Birmingham - HooverGA - Atlanta 30341*
Atlanta - Sandy Springs - Marietta
2019 Audi Q8 Return Unit Going Back If Interetsed**
(SUV)
dealer to dealer - ask for Brit 774-855-6046
Order ID:*BM25023(No Carrier Assigned)
158 mi*($1.27/mi)
11/26/2019
11/26/2019

This is two sets of many auto transported loads in the column I have. After the second duplicate date (for example after the second 2/24/2020) the new load information starts. What I need to do is separate this information into multiple columns to evaluate the information better. I want each row in the new columns to contain the right information (I don't want my order data to become jumbled up because then that defeats the purpose of examining the orders through multiple columns). Below is how I would like the data broken up using the first load example above:

Data_________________________________________Columns to create

Birmingham - HooverGA - Atlanta 30341*__________Pick up Location
Atlanta - Sandy Springs - Marietta_________________Delivery Location
2019 Audi A5 Sportback*_________________________Vehicle Type
(Car)_________________________________________ General type
dealer to dealer - ask for Brit 774-855-6046__________Transaction Type
Order ID:*BM26253(No Carrier Assigned)____________Order ID (Only need the order# BM26253)
158 mi*($1.27/mi)______________________________Miles & $ Per Mile
2/24/2020 ____________________________________Date of Move
2/24/2020_____________________________________**This second date is not needed**
Enclosed______________________________________Enclosed? (I know that Enclosed is not in the data above but this is in some of the data orders, there is a couple in the example I attached)

So I thought of using the Offset formula but the downside was that some of these data orders are not perfectly formatted. What I mean by that is the two orders provided are exactly 9 rows long. Throughout the 10,000 rows in the column I would come across some data orders that are 10 rows long or some that are 8 rows long. This threw off that formula for me.

Does anyone have any suggestions?

Thanks.

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

Hi jsts and welcome to the forum,

After looking at your data it looks like you want to "skip" the cells that have "Enclosed" and "Zips not verified". Is your data consistent enough that these are the only two problem cells? I'd be writing some VBA code to step down your data and tossing it into the correct columns and skip the above two words. If a VBA answer is not possible then how about deleting and "Move up" those two words and do your offset solution?

Thank you,

The data I pulled is not consistent there are other factors that would disrupt the Offset formula. I would like to try the VBA method although I do not have much experience in working with that. I am willing to do the research regarding the VBA mthod I am just not sure where to start.

Edit** Providing a little more information on why Offsett wont work. Sometimes we have orders with more than one vehicle on them. For the order data this would result in one line per vehicle getting transported For example it would look like this:

Riverside - San Bernardino - OntarioTX - Dallas*
Dallas - Fort Worth - Arlington
2019 Lexus Ls 500
2019 Lexus Rx 350
(Car, SUV)
manheim to dealer - Call and ask for Kenny 774-855-4287
Order ID:*KB25460(No Carrier Assigned)
1,394 mi*($0.54/mi)

The additional Lexus being transported creates an additional line. There are multiple orders with situations like this.

Should there be two rows of data for your (Car,SUV) example above? The computer/person that produces this report wasn't a database expert!

Could you have something like (Car, Car, SUV, Car,SUV) in your delivery schedule? When does it stop? Offset doesn't seem to work with inconsistent data like you show. Is there always a single Pickup and Destination in your data or is a single Order ID?

To do VBA we'd need a sample with all those "funny" orders listed. Then an attempt of some VBA would be worth doing. Can you expand your sample file and let us chomp on it?

Marv

Hi JSTS,

See the attached where I've done what I could. It seems your data is inconsistent enough to not be unscrambled to my satisfaction. Perhaps you can follow my VBA logic where I look for the Order ID: and key from it. See if you can use what I've done. You might use my result to sort by date or dollar and then do a Find using my results to see what was really in Column A.

Car Delivery Unscramble.xlsm