Want to repalce single keyword with multiple keyword with multiple versions ,Please help :)

[amolpomane]

I have a keyword list in one coloumn and I want to replace a single keyword with the list of keywords separately to make a


different versions of same ads for my adword campaign ,


E.g.


Column A


birbal story book
birbal book
akbar and birbal story books
birbal story books
birbal books
price of birbal story book
best deals on birbal story book
cost of birbal story book
purchase birbal story book
low cost birbal story book
latest birbal story book
sale on birbal story book
buy birbal story book
online birbal story book
where to buy a birbal story book
where to buy birbal story book
buy a birbal story book
deals on birbal story book
offer on birbal story book
how to buy birbal story book
how to buy a birbal story book
birbal story book online
birbal story book price
birbal story book prices






I want to replace word "BIRBAL" by the list of keywords and want to create a separate copies of each ,I want to replace this with


***************
Albert Einstein
Buddha
Kabir
Mirabai
Sai Baba
****************


I want separate version of each list which is replaced by a keywords from above list may be in one sheet or another .
I want to create a adcopies for them .
Is it possible ?

 

[Rick Rothstein]

Instead of making us guess what you want for a final outcome, why don't you pare down your lists and then show use how you want to use the second list in it. For example, let's say your first list is this...

Column A
--------------
birbal story book
birbal book
akbar and birbal story books

and your second list is this...

***************
Albert Einstein
Buddha
****************

(By the way, where is this second list at?) Okay, given the above pared down lists, and assuming you want to replace word "BIRBAL", what did you want the output from whatever we come up with to look like?

 

[amolpomane]

Hi Rick,

I want to replace a "birbal" word by a list which is in separate sheet and want to kae a different versions of it ,let me show it ,what I want .I have a list in sheet1 given below

Column A
--------------
birbal story book
birbal book
akbar and birbal story books

and my second list is in separate sheet2 ,now i want to repalce "birbal"keyword from the words presents in following list

***************
Albert Einstein
Buddha
****************

*****Output will look like ******
Albert Einstein story book
Albert Einstein book
akbar and Albert Einstein story books

Buddha story book
Buddha book
akbar and Buddha story books

****************************************
All in same sheet
I hope i cleared what I want ,please let me know any questions

 

[Marcelo Branco]

Hi,

Supposing your sentences are in A1:A24 and the list of five keywords in A27:A31 try this formula in C1

=SUBSTITUTE($A1,"birbal",INDEX($A$27:$A$31,COLUMNS($C1:C1)))

Copy (drag) down till C24 and across to columns D, E, F, G

Adjust the columns width

M.

 

[Rick Rothstein]

Here is a macro that will ask you for the keyword you want to replace and then create the list you asked for...

Sub MakeAdCampaign()

  Dim X As Long, Z As Long, TitlesLastRow As Long, KeywordsLastRow As Long, OutRow As Long
  Dim TWS As Worksheet, KWS As Worksheet, OWS As Worksheet, ReplaceWord As String
  Dim Titles As Variant, Keywords As Variant, OutList As Variant

  
  '  First list
  Const TitlesSheet As String = "Sheet1"
  Const TitlesStartRow As Long = 1
  Const TitlesColumn As String = "A"
  

  '  Second list
  Const KeywordsSheet As String = "Sheet2"
  Const KeywordsStartRow As Long = 1
  Const KeywordsColumn As String = "A"
  

  '  Destination
  Const OutputSheet As String = "Sheet3"
  Const OutputStartRow As Long = 1
  Const OutputColumn As String = "A"
  

  Set TWS = Worksheets(TitlesSheet)
  TitlesLastRow = TWS.Cells(Rows.Count, TitlesColumn).End(xlUp).row
  Titles = TWS.Cells(TitlesStartRow, TitlesColumn).Resize(TitlesLastRow - TitlesStartRow + 1)
  

  Set KWS = Worksheets(KeywordsSheet)
  KeywordsLastRow = KWS.Cells(Rows.Count, TitlesColumn).End(xlUp).row
  Keywords = KWS.Cells(KeywordsStartRow, KeywordsColumn).Resize(KeywordsLastRow - KeywordsStartRow + 1)
  

  Set OWS = Worksheets(OutputSheet)
  ReplaceWord = InputBox("Enter the word you want to replace...", "Enter Replacement Word")
  If Trim(ReplaceWord) = "" Then Exit Sub
  

  OutRow = OutputStartRow
  For X = 1 To UBound(Keywords)
    ReDim OutList(1 To UBound(Titles), 1 To 1)
    For Z = 1 To UBound(Titles)
      OutList(Z, 1) = Replace(Titles(Z, 1), ReplaceWord, Keywords(X, 1))
    Next
    OWS.Cells(OutRow, OutputColumn).Resize(UBound(OutList)) = OutList
    OutRow = OutRow + UBound(OutList) + 1
  Next
  

End Sub

I included several constants (the statements that start with Const) that you can set to control where the program looks for its data and where the output should go. This gives you the flexibility to move things around in anyway you might want to. Hopefully the names of the constants are self-explanatory (if not, please feel free to ask for clarification).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MakeAdCampaign) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

 

[mclifford82]

Marcelo,

That is a very clever solution. Great formula.

 

[Marcelo Branco]

Marcelo,

That is a very clever solution. Great formula.

Thank you, but let's see if it's what the OP wants.

M.