+ Reply to Thread
Results 1 to 5 of 5

Extract unique value from column of consecutive repeated values

  1. #1
    Registered User
    Join Date
    07-07-2016
    Location
    Kuwait
    MS-Off Ver
    2016
    Posts
    28

    Extract unique value from column of consecutive repeated values

    Hi,

    I have a column of numbers, some of which are repeated consecutively, and I want to extract a single value for each of these instances. Eg the column could look like:

    2
    2
    6
    4
    4
    4
    12
    12
    2
    2
    2
    5
    5
    4
    4
    15
    15
    etc


    I want to 'collapse' each consecutive repeated value into just a single value. I.e. like this:
    2
    6
    4
    12
    2
    5
    4
    15
    etc

    So, it's not just a simple case of extracting unique values - since the non-consecutive repeats (eg 2,4) would not be preserved.

    Any suggestions?

    Thanks,

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

    Re: Extract unique value from column of consecutive repeated values

    This proposed solution uses a helper column with the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The column that yields the second set of values from post #1 (Result) is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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.

  3. #3
    Registered User
    Join Date
    07-07-2016
    Location
    Kuwait
    MS-Off Ver
    2016
    Posts
    28

    Re: Extract unique value from column of consecutive repeated values

    Thanks a lot - that worked well.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,911

    Re: Extract unique value from column of consecutive repeated values

    Another way ...

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$17)/($A$1:$A$17<>$A$2:$A$18),ROWS($A$1:A1))),"")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-07-2016
    Location
    Kuwait
    MS-Off Ver
    2016
    Posts
    28

    Re: Extract unique value from column of consecutive repeated values

    Thanks - that works too.

+ 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. counting consecutive repeated values in a column along with the range
    By g s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2005, 04:05 PM
  2. [SOLVED] create list of unique values from a column with repeated values?
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 12:05 PM
  3. [SOLVED] create list of unique values from a column with repeated values?
    By Chad Schaben in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. create list of unique values from a column with repeated values?
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  5. create list of unique values from a column with repeated values?
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  6. create list of unique values from a column with repeated values?
    By Chad Schaben in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] create list of unique values from a column with repeated values?
    By Chad Schaben in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. create list of unique values from a column with repeated values?
    By Chad Schaben in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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