MACROS to make Sheet 1 look like Sheet 2 (Re arranging)

Hello, im new to macros and was wondering if its possible to re arrange sheet 1 to look like sheet 2 in the file attached. So something like:

If any text string in column A looks like "chain" then put this into cell 1 Column A<--so running something like this should put "Chain: 12345678" into the first cell in column A since thats the only text value on the excel doc with the word "chain in it" (See sheet 2 for reference".


Thanks!goodsample.xlsx

Hey taimysho0,

You could do something like the following as a starting point:

Please Login or Register  to view this content.

Some notes about the macro:

• Run the macro on your sheet 1.
• This looks for the values within Range("C1:C1000") (Formally Range("A1:A1000") before adding in the two columns). You should change this accordingly
• You could make the values that it looks for variable by keeping a running list on another sheet and using that list to populate Column A.

hello thanks so much for the help! much appreciated.

however it looks like this code will be reliant on sheet 2. is there anyway to hardcode the macros to be structured like sheet 2 without sheet 2 actually being there? The way my users will use the macros is detailed below:


1)user receives a word doc with contents
2)user opens new instance of excel and paste contents from word to excel
3)user hits macros button and data reorganizes and gets rearranged to look like the example in sheet 2.

^i hope im a little more clear, sorry if im not but thanks for the help!!!

Do you mean like this?

Originally Posted by JustinGlasgow

Hey taimysho0,

You could do something like the following as a starting point:

Please Login or Register  to view this content.

Some notes about the macro:

• Run the macro on your sheet 1.
• This looks for the values within Range("C1:C1000") (Formally Range("A1:A1000") before adding in the two columns). You should change this accordingly
• You could make the values that it looks for variable by keeping a running list on another sheet and using that list to populate Column A.

hello justin, this looks like it works best, however, how do i delete the rest of the data after it reorganizes it? basically everything on the right afte rmacros runs. Also i need to add "merchant_" in front of each of those fields after it gets reorganized, any way to do that??

ex: merchant_chain instead of Chain

Originally Posted by jindon

Do you mean like this?

thanks jin for helping out. however, it looks like this generates the file on spreadsheet 2, any way to keep it on spreadhseet 1m and as mentioned above, after macros runs, delete all the original information and add prefix "merchant_" to each field? thanks

See code below:

Please Login or Register  to view this content.

I assumed that you didn't want Merchant_Merchant Name and only wanted Merchant_Name. If that is not the case, replace:
CODE]
If Left(Range("A" & i).Value, 8) <> "Merchant" Then
Range("A" & i).Value = "Merchant_" & Range("A" & i).Value
Else
Range("A" & i).Value = Replace(Range("A" & i).Value, "Merchant ", "Merchant_")
End If
[/CODE]


with:

Please Login or Register  to view this content.