Help Rearranging data into rows from 2 columns

Hi, I am Gary and I am new here, although I have been using the site for months and have picked up a lot of information as I am a novice. I have been working on a sheet that reads a csv file and uses a lot of =IF formulas and then ticks certain boxes, transfers phrases etc and I was really happy with how I was getting on but then the csv has all been changed and the data is not reliable. My only option now is to copy information from an email I receive and hand transfer the information.

I have realized that I can use the formulas I have created originally in a new sheet but only if I can get the data to display correctly and I am really struggling after searching the internet and this site.

I have attached the spreadsheet with the data I need help with. This is a passenger booking system where it tells me who they are, where they are travelling to and from, times, disabilities and other data.

When I paste the data, it forms into 2 columns that are 15 rows per passenger. A1 to A15 and B1 to B15.

Firstly, I need the information in B1 to B15 to stack below the data in A1 to A15 and then I need it to transpose so all that data is in row 1. I can do this manually but it is time consuming as sometimes I could have over 100 of these.

Secondly I need it to do the same for each passenger. EG, passenger A in row 1, Passenger B in row 2 etc

Also, this seems to leave the same columns blank for each passenger, so I need it to delete the blank columns as they will always be the same and of no use.

If anyone can help then I would be grateful, as it means i can then use my old formulas to read this new data.

Thank you

Hi Glenn, This is amazing and i have it working now but not 100%. When i paste the formula in A1 on a sheet with 24 journeys, it does exactly what i want it to do. But when i then paste the S1 formula, it only copies the first 3 journeys and doesnt read the rest. Is it something i am doing wrong. I have tried to drag the formula down but i get a #spill!
Thank you Gary

Hansdouwe, thank you so much.

Glad it work now.
Thx for the rep.

Ok, I am sorry for this but I have hit a snag... We have today changed over to a web based email. This means that when I copy the data from the email, it is now different and has a few more blanks..

I still need the same thing to happen, all the data that is in column B to stack under the data in column A and then to transpose into a row. I need a new new row for each passenger. The old format had 15 rows but the new format is different.

The new format seems to have a small anomaly. For the first passenger, the data is in A1 to A16 and B1 to B16 as there is no gap between the data in A4 ("arrive" or "Depart") and A5 ("pax leg origin"), all other passengers have a gap between the "arrive" or "depart" cell and the "pax leg origin" cell.

To even things up, I could manually add a row between A4 and A5 and then there would be symmetry for all passengers.

The main thing now though is that the 2 formulas provided dont work.

Can you help me out with the corrected formulas please?

Originally Posted by worth1g

For the first passenger, the data is in A1 to A16 and B1 to B16 as there is no gap between the data in A4 ("arrive" or "Depart") and A5 ("pax leg origin"), all other passengers have a gap between the "arrive" or "depart" cell and the "pax leg origin" cell.

Its important the forms of all passengers have the same lay-out.
Decide which layout you want to start with and where you want to go and a similar formula can be created for that.

Hi Glenn, This seems to work perfectly. I do not need any of the blank columns. They appear in the same column every time and are just surplus so they are not needed.
I cant thank you enough for all the help.
I am now going to put this into the form i created last time and amend the formulas in that form.
I may need help with the final bit of that form too but i will create a new post when i am at that point. Thanks again.

Originally Posted by kvsrinivasamurthy

Give the expected result for the file in Post#7.
VBA solution is ok?

Thanks for the reply, Glenn has managed to sort this below. I may be creating a new post at some point as i know what i am doing with this data but there is one part i am stuck with. I just need to complete the form first.

Thanks for the reply, Glenn has managed to sort this below. I may be creating a new post at some point as i know what i am doing with this data but there is one part i am stuck with. I just need to complete the form first.