Home

Organizing Excel columns based on certain text

Highlighted
Jani_Atte
Occasional 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
Highlighted

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

Highlighted

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.

 

Highlighted
Please attach the sample for reference.
Highlighted

Here a shorter version (with reduced data)

Highlighted

Eh, once again

Highlighted

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

Highlighted

Now attached to this message. Do you see it?

Highlighted

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

 

 

Highlighted

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

Highlighted

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

Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
0 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
3 Replies