Distribute the data from Single Column to multiple Columns

Hi

I want to run a macro to Distribute the data from Single Column to multiple Columns

For example
I have a data in Sheet1 - Column B - Rows Could be 2 to 10000

Where the data will be in sequence,

Please Login or Register  to view this content.

Now I want the above data in Sheet 2 like

Please Login or Register  to view this content.

I'm doing this part manually but looking for help to automate it

Flaws in Data:
Projects are not limited
Some times All files comes from single project, and some times files comes from many projects

Catch is, the Project Name starts with word "Project".. Then File names continues...

Additional Information: The above data is been processed from a text field and arranged in an order. Basing on the File Name the Project is been set. Mapping table, Formulas & Macros I use are available in the attached workbook.

Is there a way to automate this part? Need your help...

Thank you

Data_Sample.xlsm

You can try this code

Please Login or Register  to view this content.

Thanks for the quick reply... macro is doing as said...

Can we input multiple project names, in case like working on two or more projects like, "Quick_Project_Core" & "Old_Project_Maintenance", (length of the word is not fixed) and it should check for either of the words and then segregate the data...

Thanks Mike.... Got it what need to be done... Thanks a lot

Thanks Mike, With the help of your code, I have changed a line and got it. I'm want to add more letters to word "Project" and get the desired output....

Modified line, may be lengthy, but still doing its part...

Please Login or Register  to view this content.

I wish to have good VBA programming skills, yet to learn from experts like you and others on our forum...

Thank you...
Data_Sample1.xlsm