Organizing Excel columns based on certain text

Copper Contributor

There is a big amount of data that needs to be re-format for other tools.

Customer send the data in following format, where the 123X00 means, that parts from manufacturers a, b or c can be used. The rule applies to only codes ending with X00.

See below.

 

123X00dsfsfa sdfsdfsbasdfasdfc

 

Our system can input such data only in format, where 123X00 is written on separate rows, such as below. So the customer data must be "transformed" into format below.

 

123X00dsfsfa 
123X00sdfsdfsb
123X00asdfasdf

c

 

 

Do you have an idea, how to get such re-formatting automated, as there are thousands of rows that can not be handled manually

10 Replies

I prepared a VBA. Let's see if it is helpful to you.

Thanks. I can see you know VBA well.  I can see my original description was not clear.

Attached some of the real data with example what the real result should look like. I can understand little bit what is happening with the VBA code you created, but I am not able to fix it from there..

I discovered that I need more complex VBA. I need to list all the part numbers and manufacturers for each Customer code ending with X00. On top of that, I need to list each separate code as a new row. Please see attached example... I try to ask local help also, but would like to see if this is possible to do with VBA.

 

Please attach the sample for reference.

Here a shorter version (with reduced data)

Eh, once again

Sorry.... I do not see any attachment in your article.

Now attached to this message. Do you see it?

I prepared another VBA for your reference. It also included more comment. Hope that it is helpful. 

 

 

Thanks, I could not find the VBA inside the file. Can you please re-check? OR maybe I don't know how to use it....?

 

br, Jani

Sorry.... I don't know why the file does not contain VBA..... I re-write it again. Please see the attachment.