+ Reply to Thread
Results 1 to 16 of 16

Find multiple sequense values

  1. #1
    Registered User
    Join Date
    09-24-2015
    Location
    Athens
    MS-Off Ver
    2007
    Posts
    3

    Find multiple sequense values

    I have a huge database and I need to pull certain data from the database. I know how to search the database for single values, but I need to make a more advance search for multiple sequence of values.Not sure if it can be done but I did search for a similar thread in this forum and didnt find any. Any help is welcome.

    I made this sample to make it much moree simplier.Lets say i want to search three values. 23 26 8. These values appear in row 3 5 and 7. So i want a formula to make a search in all rows for numbers 23 26 8 and give results only when all those 3 numbers appear in a row no matter the order or if other values interfering between those three values, highight those rows and write in cells the number of the rows that exist those values or write the date or time that those values appear.

    Is my question understandable? Let me know if u have any thoughts


    Thanks

    Spiros.xlsx

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Find multiple sequense values

    make column J for search purpose
    formula in J will be
    =IF(ISERROR(MATCH(23,C3:I3,0)+MATCH(26,C3:I3,0)+MATCH(8,C3:I3,0)),0,ROW())
    this will return the row no

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find multiple sequense values

    You could also use.

    =PRODUCT(COUNTIF(C3:I3,{23,26,8}))

    Enter the formula in J3 and fill down, then use a filter to hide rows with a 0 value.

    With large data volumes, you may find that one method calculates faster than the other.

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find multiple sequense values

    Or in column J (or whichever column you want to display the result)...

    To display the date you could use this formula and drag down

    Please Login or Register  to view this content.
    or to display the Date and Time, this

    Please Login or Register  to view this content.
    or to conditionally format the rows, apply this to $A$3:$I$7 (obviously to the entire range on your database) and choose a highlight colour

    Please Login or Register  to view this content.
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Find multiple sequense values

    Here are two formulae that will determine if the values 23, 26, 8 are in a row. Enter this in the column at the end of the rows and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or this Array formula entered with Ctrl + Shift + Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To list all of the rows in a separate range without spaces between the values enter this Array formula entered with Ctrl + Shift + Enter and fill down until data runs out. I used column N.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Column L is where I chose to enter either of the first 2 formulae.

    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    2
    3
    3
    1
    2
    6
    23
    26
    8
    11
    3
    5
    4
    24
    7
    10
    9
    20
    13
    19
    7
    5
    11
    23
    26
    25
    8
    5
    4
    5
    6
    19
    9
    16
    14
    22
    11
    1
    7
    3
    8
    23
    13
    11
    24
    26
    7
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find multiple sequense values

    Quote Originally Posted by newdoverman View Post
    To list all of the rows in a separate range without spaces between the values enter this Array formula entered with Ctrl + Shift + Enter and fill down until data runs out. I used column N.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    instead?

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find multiple sequense values

    Assuming there will not be duplicates in any row (as your sample file demonstrates).

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    2
    time
    date
    3
    10:55
    6/22/2014
    1
    2
    6
    23
    26
    8
    11
    10:55
    6/22/2014
    4
    11:22
    3/2/2015
    24
    7
    10
    9
    20
    13
    19
    0:23
    4/17/2015
    5
    0:23
    4/17/2015
    11
    23
    26
    25
    8
    5
    4
    8:00
    8/15/2015
    6
    17:56
    7/20/2015
    19
    9
    16
    14
    22
    11
    1
    7
    8:00
    8/15/2015
    3
    8
    23
    13
    11
    24
    26


    This array formula** entered in K3:

    =IFERROR(INDEX(A:A,SMALL(IF(MMULT(--ISNUMBER(MATCH($C$3:$I$7,{8,23,26},0)),{1;1;1;1;1;1;1})=3,ROW(C$3:I$7)),ROWS(K$3:K3))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to L3 then down until you get blanks.

    Format column K as h:mm
    Format column L as Date
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Find multiple sequense values

    Quote Originally Posted by jason.b75 View Post
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    instead?
    You can even use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There are many ways of creating the list including Advanced Filter if you like
    P
    1
    Rows
    2
    >0
    3
    4
    Rows
    5
    3
    6
    5
    7
    7

  9. #9
    Registered User
    Join Date
    09-24-2015
    Location
    Athens
    MS-Off Ver
    2007
    Posts
    3

    Re: Find multiple sequense values

    i am keep getting an error. i am trying to use the array formulas with cntrl shift + Enter but keep geting an error. tried all the solutions here but it seems i am doing something wrong.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find multiple sequense values

    What error are you getting?

  11. #11
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find multiple sequense values

    Hi spiraklas,

    Sample attached for my suggested solutions, no array formula's involved...
    Attached Files Attached Files

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find multiple sequense values

    Here's a sample file that demonstrates my suggested formula.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-24-2015
    Location
    Athens
    MS-Off Ver
    2007
    Posts
    3

    Re: Find multiple sequense values

    Spiros.xlsx

    ok .lets assume i have this database... i just repeat the first data so i can have near thousand rows. i want to create a forumla so i can search for those numbers only in rows (23,26 ,8) not necessary with that order or it doesnt matter if others numbers appear between those numbers. just want to check if those numbers exist in any row. i want it to show the rows that those values exist in cell k1202 and highlight them in the data. also i dont want to use a formula that will drag it down. Because this database contains few dozen of thousands values,, canoot drag it...prefer to give some limits.

    i tried also other formulas but i am receiving same error. <we found a problem with this formula , try clicking insert function.... Does it play any role that i have my excel in greek language? maybe separators are different or something?
    Last edited by spiraklas; 10-11-2015 at 06:44 PM.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find multiple sequense values

    It sounds like different separators, if you download the samples that Hangman and Tony have provided for you then the formula will be correct when you open them.

    Typically you have to replace comma "," with semicolon ";" but I'm not sure what you need to use in place of semicolon in a formula that already uses them.
    Last edited by jason.b75; 10-11-2015 at 07:10 PM.

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Find multiple sequense values

    I don't see how you can do exactly as you want without VBA but here is a formula based solution:
    Conditional formatting is used to highlight the rows that meet the criteria.
    CF formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Find multiple sequense values

    You can highlight the rows using the conditional formatting formula newdoverman suggests above or the formula I mentioned above which negates the need for a helper column.

    Please Login or Register  to view this content.
    However to achieve what you want to with putting all the row numbers in a single cell won't work for the number of rows you will likely have because there is a 1,024 character limit per cell, so if the number of rows containing '23, 26 and 8' exceeds this you won't be able to display all the row numbers in a single cell.

    What is the logic of listing the row numbers in a single cell, what is it you are trying to actually achieve once you've identified the rows containing '23, 26 and 8'. Conditional formatting will highlight the rows, which you could then filter by colour if you just need these rows visible but what are you needing to do then? If you can let us know maybe we can suggest an alternative approach...
    Attached Files Attached Files

+ 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. Replies: 2
    Last Post: 05-26-2015, 07:29 PM
  2. Find multiple values with multiple criteria in vba macro
    By Jovillanueva in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-02-2014, 09:46 PM
  3. Find multiple values in multiple ranges and produce results in a list
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2014, 02:11 AM
  4. [SOLVED] Faster way to find and replace multiple values with corresponding values
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2013, 06:50 PM
  5. How to find closest values in multiple columns and return adjacent values.
    By Patrician in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 01:47 PM
  6. Find and compute multiple values from multiple tables
    By canada123 in forum Excel General
    Replies: 2
    Last Post: 07-18-2011, 09:00 PM
  7. Find and Replace values from multiple lookup values
    By Gregula82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2007, 03:12 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