+ Reply to Thread
Results 1 to 4 of 4

Differentiate Complex Duplicates

  1. #1
    Registered User
    Join Date
    05-09-2020
    Location
    Yerevan
    MS-Off Ver
    )ffice 365
    Posts
    7

    Differentiate Complex Duplicates

    Hello, I would really appreciate if you could help with the following.

    As seen the attachment I have three columns named Row Number, Person Name & Fruit.

    I want to filter out the data whereby people with different names eat the same fruit.

    So, I have two sheets in the excel file- the "Input" sheet which presents the raw data, and "output" sheet which is what I want to get.

    So, I want to be able easily filter out the data based on the logic above. So in my case, output will only be the below, as these are the only raws which satisfy the condition i.e. people with different names eat the same fruit.

    3 George Orange
    5 Smith Orange

    Is there a way I can filter this out automatically in excel? Thank you very much.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Differentiate Complex Duplicates

    with a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,680

    Re: Differentiate Complex Duplicates

    Try this:

    =FILTER(Input!A2:C9,Input!C2:C9="Orange ")

    There's a trailing space after Orange in your table.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    Re: Differentiate Complex Duplicates

    In the attached I interpreted and approached this a little differently.

    I started with creating Dynamic Named Ranges so called because they resize to fit the data. They are defined this way in Name Manager.


    Fruit
    =Input!$C$2:INDEX(Input!$C:$C,MATCH(25^25,Input!$A:$A))
    Person
    =Input!$B$2:INDEX(Input!$B:$B,MATCH(25^25,Input!$A:$A))
    Row_Number
    =Input!$A$2:INDEX(Input!$A:$A,MATCH(25^25,Input!$A:$A))


    Then in Output this formula in A2 copied down and across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

+ 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. Finding Duplicates and Color Coding for differentiate
    By kotigari in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2016, 12:09 PM
  2. Complex sort returning duplicates (no really)
    By gotheframe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-02-2014, 11:41 AM
  3. Differentiate each set of duplicates with different fill color
    By sixdinnersid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2014, 01:27 AM
  4. complex count if/vlookup/sumif with duplicates
    By martacm13 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-09-2013, 10:31 AM
  5. [SOLVED] sumif differentiate between 6.1 & 6.10
    By realnouns in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-19-2013, 01:17 PM
  6. Differentiate between 00 and 000
    By Prcntrygrl in forum Excel General
    Replies: 2
    Last Post: 01-19-2011, 07:06 PM
  7. Identifying complex duplicates
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2006, 07:25 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