+ Reply to Thread
Results 1 to 8 of 8

How to Find, Highlight A Pattern In A Database And Select Row of the data below pattern.

  1. #1
    Registered User
    Join Date
    12-20-2018
    Location
    New Abirem, Ghana
    MS-Off Ver
    2016
    Posts
    11

    How to Find, Highlight A Pattern In A Database And Select Row of the data below pattern.

    i have a table A as follows




    Id C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14

    1 2 3 2 5 1 2 3 2 5 1 2 3 2 5
    2 2 4 6 2 2 7 4 6 2 2 7 4 6 2
    3 3 3 8 3 3 3 3 8 3 3 3 3 8 3
    4 3 5 3 4 9 1 9 8 3 3 3 3 8 3
    5 4 4 3 2 4 3 4 3 4 4 3 4 3 4
    6 5 5 1 4 5 4 4 3 2 6 5 5 1 1


    I need a vba code to select all patterns in my database such as when 2 on top, followed by 2 at bottom column-wise(2 LAPPING 2). Afterwards, I want the code to select the next row below the identified patterns, ie After the identified pattern, what data comes next? : IN THIS INSTANCE ONLY id3 WILL BE SELECTED because only column one (C1) contains the pattern.

    Id C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14
    3 3 3 8 3 3 3 3 8 3 3 3 3 8 3




    I hope I am clear with my problem enough. I am very new to coding!!!

    ThanksPattern Datatable.PNG

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Assuming Header starts from A1 and the data starts from A3.
    If this doesn't work on your side, upload your workbook.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-20-2018
    Location
    New Abirem, Ghana
    MS-Off Ver
    2016
    Posts
    11

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Attachment 638291

    Header is on A1:N1

    Column A contains EventID
    Column B contains Date
    C2:G1000 contains data and
    I2:M1000 contains data

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Your attachment is not working.

    I need to see your WORKBOOK, anyway.

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    12-20-2018
    Location
    New Abirem, Ghana
    MS-Off Ver
    2016
    Posts
    11

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Hi jindon

    please I've attached a sample data to illustrate my problem further, I hope this brings some clarity in what i want to point out.

    Thanks,
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Perhaps this formula based proposal will help.
    P5:Y5 are populated using: =IF(P3="","",AGGREGATE(15,6,(ROW(C$2:C$828)-ROW(C$1))/(C$2:C$827=P3)/(C$3:C$828=P3),1))
    Note that P5:Y5 could be hidden by setting font to match fill (white) for aesthetic purposes.
    P9:AA9 are populated using: =INDEX(A2:A828,INDEX($P5:$Y5,AGGREGATE(15,6,(COLUMN($P1:$Y1)-COLUMN($O1))/($P5:$Y5<>""),1))+2)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: HOW TO FIND, HIGHLIGHT A PATTERN IN A DATABASE AND SELECT Row of the data below patter

    Please do not type your thread titles in UPPER CASE - this is considered as shouting online, and we are neither deaf nor daft. It's also hard to read and will not get your question answered any faster.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    12-20-2018
    Location
    New Abirem, Ghana
    MS-Off Ver
    2016
    Posts
    11
    Quote Originally Posted by AliGW View Post
    Please do not type your thread titles in UPPER CASE - this is considered as shouting online, and we are neither deaf nor daft. It's also hard to read and will not get your question answered any faster.
    I have taken note, thanks.

+ 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. VBA code to highlight and select a particular pattern
    By ubatu05 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2019, 09:00 AM
  2. rule to highlight a pattern
    By chop83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2016, 10:30 PM
  3. Continuing a pattern on autofil without pattern re-starting
    By jakeyoung111 in forum Excel General
    Replies: 4
    Last Post: 06-22-2016, 11:11 AM
  4. Macro to insert pattern based on value and select based on pattern
    By CB569 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2015, 12:36 PM
  5. [SOLVED] highlight cells with same pattern
    By rahulsureshonline in forum Excel General
    Replies: 9
    Last Post: 12-17-2014, 12:25 AM
  6. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  7. Find pattern in data
    By tos in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-15-2010, 11:13 AM

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