+ Reply to Thread
Results 1 to 2 of 2

How to "Search and replace" a list of different names - multiple times

Hybrid View

The Excel Noob How to "Search and replace" a... 02-16-2015, 11:58 AM
The Excel Noob Re: How to "Search and... 02-17-2015, 06:10 AM
  1. #1
    Registered User
    Join Date
    02-16-2015
    Location
    Hamburg, Germany
    MS-Off Ver
    2010
    Posts
    5

    How to "Search and replace" a list of different names - multiple times

    Hey guys,

    I have a huge problem and hope to find help in this forum .

    The question is as follows:

    I have one column of words, let's say:
    example one 'gap'
    example one 'gap'
    example one 'gap'
    example two 'gap'
    example two 'gap'
    example two 'gap'
    example three 'gap'
    .. and so on.

    On the other hand there is a column with a list of names, like that:
    Name 1
    Name 2
    Name 3

    My goal is to "search and replace" the 'gap'-words with the names to have them like this:
    example one Name 1
    example one Name 2
    example one Name 3
    example two Name 1
    example two Name 2
    example two Name 3
    example three Name 1
    ... and so on.

    The whole thing gets tricky, because I have different lists of sometimes more than 1.000 names. Important sidenote: The items in the not-name-column (represented with 'example one 'gap'',....) have the same number as there are names - it looks like that:

    example one 'gap'
    (repeated for i-1 times)
    example two 'gap'
    (repeated for i-1 times)
    example three 'gap'
    (repeated for i-1 times)
    ...
    example n 'gap'
    (repeated for i-1 times)

    Name 1
    Name 2
    ..
    Name i

    Because I can't do that manually, a macro would be more than nice and a great help
    Could anybody help me there? Thanks a lot in advance!!
    Last edited by The Excel Noob; 02-16-2015 at 12:03 PM.

  2. #2
    Registered User
    Join Date
    02-16-2015
    Location
    Hamburg, Germany
    MS-Off Ver
    2010
    Posts
    5

    Re: How to "Search and replace" a list of different names - multiple times

    Hm.. good thing guys in another forum were able to help me. If anyone is curious:

    =SUBSTITUTE(C1;"gap";INDEX(A:A;REST((ZEILE(C1)-1);i)+1))

    that is if "example one 'gap'" and so on is in column C and the names in column A.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. replace all defined range names in the sheet that start with "Street" to "Road"
    By matrex in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-04-2016, 08:53 AM
  2. Replies: 11
    Last Post: 12-10-2014, 03:00 PM
  3. Find Names from a List of Names and Replace with "The Candidate"
    By viggykuppu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2014, 02:49 AM
  4. Separate cell which have "Comma" into different row & multiple each row "n" times
    By Arun ebl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2013, 11:22 PM
  5. Replies: 0
    Last Post: 02-23-2006, 01:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1