+ Reply to Thread
Results 1 to 7 of 7

Searching for specific sequence of numbers down a column?

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    2

    Question Searching for specific sequence of numbers down a column?

    Hey guys, as the title alludes, I was wondering whether it was possible to search for a sequence of numbers.

    For example, if this is down a column:

    A1: 5
    A2: 4
    A3: 5
    A4: 6
    A5: 9

    Is it possible to have a find function to search for the sequence of numbers "4 + 5" and consequently highlight cells A2 and A3?

    Thanks so much
    Last edited by 1st; 06-06-2014 at 01:35 PM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Searching for specific sequence of numbers down a column?

    Select range A2:An and use conditional formatting with rule based on formula.
    Sample one will be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Now select only A1 and add conditional formatting with rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Searching for specific sequence of numbers down a column?

    Selecting A1:A5
    Conditional Formatting>Use Formula

    =OR(AND(A1=4, A2=5), AND(OFFSET(A1,-1,0)=4, A1=5))

    format as desired
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    06-06-2014
    Posts
    2

    Re: Searching for specific sequence of numbers down a column?

    Thanks Kaper and ChemistB! This works great!

    ChemistB, I was wondering how to use your formula to highlight more numbers. I actually need to find a sequence of four (for example, from the original post: 5+4+5+6) but I'm not very skilled in Excel and don't know how to go about expanding your formula.

    Would you be able to show me a template for doing that?

  5. #5
    Registered User
    Join Date
    06-06-2014
    Posts
    2

    Re: Searching for specific sequence of numbers down a column?

    Thanks Kaper and ChemistB! This works great!

    ChemistB, I was wondering how to use your formula to highlight more numbers. I actually need to find a sequence of four (for example, from the original post: 5+4+5+6) but I'm not very skilled in Excel and don't know how to go about expanding your formula.

    Would you be able to show me a template for doing that?

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Searching for specific sequence of numbers down a column?

    It gets rather long because you need to set it up relative to each of the four positions (5,4,5,6)
    =AND(A1=5, A2=4, A3=5,A4=6)
    =AND(OFFSET(A1,-1,0)=5, A1=4,A2=5, A3=6)
    =AND(OFFSET(A1,-2,0)=5, OFFSET(A1,-1,0)=4, A1=5, A2=6)
    =AND(OFFSET(A1,-3,0)=5, OFFSET(A1,-2,0)=4, OFFSET(A1,-1,0)=5, A1=6)
    Combined
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Kaper's technique would work too, with each additional row down getting an additional conditional rule (total of 4 by row 4) Personal preference.

  7. #7
    Registered User
    Join Date
    12-14-2014
    Location
    London
    MS-Off Ver
    2007
    Posts
    1

    Re: Searching for specific sequence of numbers down a column?

    I want to identify specific sequences of numbers down the column A..
    eg.
    2
    0
    0
    1
    0
    or
    eg.
    1
    0
    0
    0
    1
    does anyone know how to do it??

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Searching for specific sequence of numbers down a column?

    Hi nilesh,
    Please do obey http://www.excelforum.com/forum-rule...rum-rules.html - namely: rule 2!
    As I refere to rules - keep on reading and below numbered list you will find:

    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    And my personal comment - include there not only sample data, but also expected (manually assessed) results.

+ 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. Formula to calculate how many numbers falls between a specific sequence.
    By headley4ever in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2013, 09:56 AM
  2. [SOLVED] Creating a sequence based on numbers in one column and adding "01,02..." to a new sequence
    By JCR1968 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-05-2013, 08:06 AM
  3. Replies: 4
    Last Post: 07-24-2012, 11:58 AM
  4. Searching for specific numbers
    By chbyers in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-17-2011, 08:16 PM
  5. [SOLVED] Repeating a sequence of numbers down a column
    By Nexan in forum Excel General
    Replies: 2
    Last Post: 03-04-2005, 02:06 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