+ Reply to Thread
Results 1 to 7 of 7

Finding duplicates between six rows

  1. #1
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    112

    Question Finding duplicates between six rows

    Hi, I have the following data:

    Please Login or Register  to view this content.
    I AM looking for a way which could find the exact duplicates between six rows of given data and also displays the column number where the first duplicate has occurred the second time .
    The desired solution starting from G1 until M1 should be as follows:
    Please Login or Register  to view this content.
    As "10,12,16,28,30,32" and "16,16,20,20,10,10" have occurred more than once in the above data, an appropriate formula should be applied so that they are the only ones to be displayed at "G1,H1,I1,J1,K1,L1"&"G2,H2,I2,J2,K2,L1". Also at M1,"6" is expected because "10,12,16,28,30,32" have occurred secondly at column number 6.

    Any kind of help towards solving this problem would be very much appreciated.
    Thanks
    Last edited by LAVA2; 08-13-2018 at 09:29 PM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Finding duplicates between six rows

    OK...Let's try this again. Not sure if the forum editor misread a formula or if I had a hardware problem (a loose nut in front of my keyboard)

    Note: These are all regular formulas...NOT array-formulas.
    Using your posted workbook, I used helper columns to keep the formulas understandable.
    These formulas, copied down, set up the items to be counted:

    Concatenate the values
    Please Login or Register  to view this content.
    Identify duplicates
    Please Login or Register  to view this content.
    This formula, copied across and down through L11, fills in the duplicate records
    Please Login or Register  to view this content.
    And this formula, copied down, returns the row number of the duplicate
    Please Login or Register  to view this content.
    Is that something you can work with?
    Last edited by Ron Coderre; 08-16-2018 at 07:13 AM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    112

    Re: Finding duplicates between six rows

    Hi Ron, thanks for helping, i did tried to use those formulas but couldn't get anywhere , thus i have uploaded a workbook with example
    please guide me so that i could know where i have made mistake.
    Attached Files Attached Files
    Last edited by LAVA2; 08-16-2018 at 05:34 AM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,227

    Re: Finding duplicates between six rows

    Ron, I'm getting the same results as well.
    Dave

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Finding duplicates between six rows

    In my haste to fix my post, I accidentally edited my original post, instead of just responding.

  6. #6
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    112

    Re: Finding duplicates between six rows

    thanks a lot ron, that was dynamic idea

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,227

    Re: Finding duplicates between six rows

    Quote Originally Posted by Ron Coderre View Post
    In my haste to fix my post, I accidentally edited my original post, instead of just responding.
    Ah yes. Very clever with the powers of 10 approach.

+ 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. Renumbering and deleting blank rows after finding and removing duplicates
    By babebull82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2016, 06:52 PM
  2. Question on finding duplicates between two rows??
    By greekCPA in forum Excel General
    Replies: 2
    Last Post: 01-30-2015, 04:17 PM
  3. Replies: 2
    Last Post: 12-27-2013, 09:24 AM
  4. [SOLVED] Finding Duplicates Rows of Data on a daily spreadsheet
    By MattyG1406 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-05-2012, 09:35 AM
  5. Need help finding duplicates between 2 rows.
    By sam121 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 03-25-2009, 09:21 PM
  6. Finding DUPLICATES or identical ROWS in a worksheet
    By Navneet in forum Excel General
    Replies: 2
    Last Post: 03-09-2009, 04:53 PM
  7. Finding rows with duplicates in two columns
    By James McMurray in forum Excel General
    Replies: 3
    Last Post: 07-18-2008, 07:03 PM

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