+ Reply to Thread
Results 1 to 6 of 6

Find and replace a pattern of cells in a single column

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Find and replace a pattern of cells in a single column

    I started a new thread because the new way I want to solve this problem is much easier to understand and is simpler to explain. Plus this new title is much more descriptive of my problem. If anyone could delete or lock my old thread for me, that'd be great as I couldn't figure out how to do it through the edit button. Here's the link to my old thread:

    http://www.excelforum.com/excel-prog...iple-rows.html

    Basically, all I need is a program that can look through a single column of data, find a pattern of cells (a pattern that stretches across multiple rows), and then replace those cells in the column with data that I define.

    I've attached an excel file with the data I'm working with. The single column I am talking about is all the data underneath the H column.

    First, I need all instances of "1,1,1,1,1,0,0,0,0,0" changed to "6,7,8,9,10,11,12,13,14,15"

    THEN

    I need all instances of "1,1,1,1,1" changed to "6,7,8,9,10"

    THEN

    Lastly, I need all instances of "0,0,0,0,0" changed to "1,2,3,4,5"

    If these are done in this specific order, then all my data will be numbered exactly how I need it. If anyone who knows of function that can do this, I would be GREATLY appreciative. Long hours of searching on Google has only given me very little results, and of those results it is either code that doesn't work or other threads like this with no one being able to solve this problem.

    Thanks for looking at my thread guys. I'd be grateful for any help.

    Cheers

    P.S. If the order of replacing these patterns is giving anyone trouble, it would not be a problem at all if this took 3 seperate programs to accomplish.
    Attached Files Attached Files
    Last edited by mundellj; 02-12-2011 at 08:44 PM.

  2. #2
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with finding and replacing a pattern of cells in a single column

    Is there not a VBA function that can do this?

  3. #3
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Find and replace a pattern of cells in a single column

    Maybe this can get you started towards a solution?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Find and replace a pattern of cells in a single column

    Thanks for the response WHER. That looks good, but how did you get the numbers into those new columns? Or did you just manually do it?

  5. #5
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Find and replace a pattern of cells in a single column

    see attachment, run macro "test"
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Find and replace a pattern of cells in a single column

    watersev, it works great! Thanks for your help, I've been trying to figure this out for days!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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