+ Reply to Thread
Results 1 to 4 of 4

Formula to generate unique list in same sequence as original

  1. #1
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Formula to generate unique list in same sequence as original

    BACKGROUND (in case it helps)
    I asked a similar question on another thread - remove immediately sequential duplicated values

    In that thread, numbers were allowed to appear more than once in column C but not in immediately adjacent rows
    One of the solutions provided used AGGREGATE to avoid requirement to confirm with {CTRL}{SHIFT}{ENTER}
    In B2
    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$21)/($A$2:$A$21<>$A$1:$A$20),ROWS(B$2:B2))),"")

    THIS REQUEST (see image below)
    Each value can appear only ONCE in column C and must be in the same sequence as the first occurrence in column A

    I can get the numbers I want with this formula in column B
    =IF(COUNTIF($A$2:A2,A2)=1,A2,"")

    But I do not want any blank cells in between the values

    I will be grateful for any solution, but is there is as a way to modify the above formula and thus avoid {CTRL}{SHIFT}{ENTER} ?
    There are no blank cells in column A
    The cells contain postitive integers < 1000

    Many thanks

    RemoveDuplicates.jpg
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula to generate unique list in same sequence as original

    Like this? =IFERROR(INDEX($A$2:$A$21,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$21),0,0),0)),"")
    Click the * to say thanks.

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Formula to generate unique list in same sequence as original

    Like this?
    Just like that!
    Thank you

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula to generate unique list in same sequence as original

    No worries, glad to help

+ 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: 4
    Last Post: 01-04-2022, 03:58 AM
  2. [SOLVED] Creating unique list of values from multiple columns not in sequence.
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-09-2017, 04:47 AM
  3. compile two columns according sequence of unique list
    By shukla.ankur281190 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-06-2016, 02:34 AM
  4. Generate Unique List 2 Columns
    By DKolev in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 07-16-2014, 05:22 PM
  5. [SOLVED] Generate Unique list
    By Cavinaar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2013, 03:03 AM
  6. [SOLVED] Need formula to generate sequence
    By singhabhijitkumar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2012, 03:46 AM
  7. Replies: 5
    Last Post: 11-16-2011, 01:25 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