+ Reply to Thread
Results 1 to 5 of 5

Return consecutive duplicates and non-duplicates from a column.

  1. #1
    Registered User
    Join Date
    06-28-2021
    Location
    virginia, usa
    MS-Off Ver
    office 365
    Posts
    3

    Return consecutive duplicates and non-duplicates from a column.

    I have the following data set in Column A of sheet 1.
    CASE
    1-123
    1-123
    1-123
    5-001
    1-111
    1-123
    1-001

    I would like to return all consecutive duplicates & non-duplicates onto Column A of sheet 2. DESIRED OUTPUT:
    CASE
    1-123
    5-001
    1-111
    1-123
    1-001

    This is what I've got so far.
    I made a helper column. Next to the first CASE I put a 1. Below I put the formula =if(A3=A2,2,1).
    CASE
    1-123 1
    1-123 2
    1-123 2
    5-001 1
    1-111 1
    1-123 1
    1-001 1

    I then entered the formula =INDEX(A2:B8,MATCH(1,B2:B8,0),1) which returned
    1-123
    5-001
    5-001
    5-001
    1-111
    1-123
    1-001

    Any help would be much appreciated!
    Sorry for the ugly formatting, I'm using free Excel off OneDrive so I'm having issues downloading my spreadsheets.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,657

    Re: Return consecutive duplicates and non-duplicates from a column.

    You have two options, with or without helper.
    With helpers:
    B2:
    =IF(A1=A2,"",MAX($B$1:$B1)+1)
    C2:
    =IFERROR(INDEX($A$2:$A$8,MATCH(ROW(1:1),$B$2:$B$8,0)),"")
    Without helper:
    B2:
    Please Login or Register  to view this content.
    Drag down.
    Attached Files Attached Files
    Last edited by bebo021999; 06-28-2021 at 09:41 PM.
    Quang PT

  3. #3
    Registered User
    Join Date
    06-28-2021
    Location
    virginia, usa
    MS-Off Ver
    office 365
    Posts
    3

    Re: Return consecutive duplicates and non-duplicates from a column.

    Solved!
    Thanks!
    ($A$1:$A$7<>$A$2:$A$8),ROW(1:1)
    So if A1 to A7 does not equal A2 to A8, go down a row?

  4. #4
    Registered User
    Join Date
    06-28-2021
    Location
    virginia, usa
    MS-Off Ver
    office 365
    Posts
    3

    Re: Return consecutive duplicates and non-duplicates from a column.

    Also, is there a way to make the formula dynamic because Column A will continue to have new entries?

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,657

    Re: Return consecutive duplicates and non-duplicates from a column.

    Quote Originally Posted by excelhelpfred View Post
    Also, is there a way to make the formula dynamic because Column A will continue to have new entries?
    Expand the range to, i.e, 100:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$101)/($A$1:$A$100<>$A$2:$A$101),ROW(1:1))),"")

+ 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. Find duplicates and copy/paste duplicates to new column
    By HometownHero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2020, 04:02 AM
  2. Replies: 6
    Last Post: 11-27-2018, 07:17 AM
  3. [SOLVED] Identification of duplicates numbers in a column and print as duplicates with that number
    By kswapnadevi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-16-2017, 05:25 AM
  4. Replies: 1
    Last Post: 07-30-2014, 02:37 PM
  5. [SOLVED] Find duplicates in column A, add values from column B (possibly delete duplicates)
    By luarwhite in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2013, 04:34 PM
  6. [SOLVED] Find duplicates, concatenate into different column, sum and delete duplicates
    By rosannang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 11:23 AM
  7. Replies: 5
    Last Post: 07-26-2012, 08:46 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