+ Reply to Thread
Results 1 to 14 of 14

Flag whether a unique or matching number follows 3 unique numbers immediately in a column

  1. #1
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Flag whether a unique or matching number follows 3 unique numbers immediately in a column

    Hi
    I have a column with 1,2,3,4 randomly throughout.
    I am looking to mark the first occasion where I get any three of the four numbers...and then if the immediately following number matches any of the three unique numbers that have appeared (match)
    If I get any three of the four numbers and the immediately following number is not a repeat, then I'd like to mark that too (no match)

    So 1,2,3,3 would be an example of a match

    But so would 3,2,1,1

    And so would 4,4,3,3,3,2,4

    And so would 4,1,4,1,2,1

    I need to check only the first number immediately following the three unique results have appeared. So 4,1,1,4 wouldn't be checked, because only two unique numbers have appeared. 4,1,1,4,3,4 would be a match.

    The number to check could appear in just 4 numbers but might not for 10 or even 20...hence difficulty in formulating.

    4,3,2,1 would be a no match (three unique numbers and the fourth is also unique)
    as would 4,1,4,1,2,3

    These are distinct episodes - so once we know if we have a match or no match, we start tracking the numbers again.

    Here's hoping...
    Last edited by Supersadie; 06-04-2018 at 11:09 AM. Reason: Correction

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Having a difficult time visualizing this.

    Please upload a sample Excel workbook with representative samples ... 10 to 20 of them usually suffice. Be sure to hand type expected results from that data with clear explanation of the logic behind it.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Yes. Sounds more complex than it is, in terms of the concept anyway, not necessarily the formula!
    Here's my mockup.
    The difficulty is that no-one knows how long the third unique number will take to appear. If it was just three results, it might be easier!
    Many thanks for your help.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    In your "real" data are there blank rows as shown in your file or is the data contiguous?

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    D3=IF(D2="Trigger",IF(COUNTIF(A2:INDEX(A:A,LOOKUP(2,1/(A$2:A3=""),ROW(A$2:A3))),A3)=0,"No Match","Match"),IF(SUMPRODUCT((COUNTIF(A3:INDEX(A:A,LOOKUP(2,1/(A$2:A3=""),ROW(A$2:A3))),{1,2,3,4})>0)+0)=3,"Trigger",""))
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Hello John
    You're right - data is contiguous.

  7. #7
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Samba, just checking - where I have the word 'trigger' in my example, this is just to show where the formula should be picking up the third unique value. There are only numbers in the data.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Show your real data with expected result

  9. #9
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    I think I have, nflsales
    The 'trigger' is to explain what I want the formula to do.
    Hope that helps.

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    D3
    Please Login or Register  to view this content.
    Try this and copy towards down

  11. #11
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Thanks nflsales. It isn't quite what I was expecting, but it actually overlaps groups, if that makes sense, which is better!
    You've given me more than I thought I wanted. And more useful than I hoped!
    Big thanks again.

  12. #12
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    The devil was in the detail.

    Looking more closely, I can see these are not overlapping, but possibly cumulative - ie it appears to be looking at cells 1:3 and then 1:4 and then 1:5 etc.

    Even so, there are inconsistencies - I can't see why some cells are being triggered to make a match, and I can't see where the unique numbers are that it's trying to match against.

    I am attaching my original mockup and some of the results of the formula to see if that clarifies. Ideally, it should re-track the new numbers after each match/no match, but if it does it on a rolling basis, that doesn't matter as long as it's still tracking three unique numbers and an immediate fourth match.

    Thanks for bearing with me.
    Attached Files Attached Files

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Flag whether a unique or matching number follows 3 unique numbers immediately in a col

    Having difficulty setting the "break" points where a new series begins.

    Is there room in the mission for a helper column(s)?

  14. #14
    Forum Contributor
    Join Date
    08-23-2010
    Location
    uk
    MS-Off Ver
    365 on Mac
    Posts
    146
    Quote Originally Posted by FlameRetired View Post
    Having difficulty setting the "break" points where a new series begins.

    Is there room in the mission for a helper column(s)?
    Most definitely!

+ 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. For all unique in Column A, return number of unique in another column
    By spot2018 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2018, 12:26 PM
  2. [SOLVED] Matching Unique Numbers and Letters
    By Brian.Aerojet in forum Excel General
    Replies: 10
    Last Post: 02-16-2018, 05:58 PM
  3. Replies: 3
    Last Post: 08-24-2017, 05:59 AM
  4. Replies: 8
    Last Post: 05-26-2016, 06:00 PM
  5. Replies: 7
    Last Post: 03-21-2016, 09:00 AM
  6. [SOLVED] Count the number of Unique Items in a Column for each Unique Item in another Column
    By HangMan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2015, 01:18 PM
  7. Count the number of unique Numbers in a column
    By ajajmannen in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-10-2006, 01:10 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