+ Reply to Thread
Results 1 to 8 of 8

Returning unique values based on duplicate criteria

  1. #1
    Registered User
    Join Date
    04-14-2021
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Returning unique values based on duplicate criteria

    Hi Everyone,

    You've saved me before, and I was hoping you would be able to help me out again.

    I have a large data set that contains many lines of product information (raw data table). I am creating an analysis table that extracts data from the raw data table.

    Ultimately, I would like to use serial numbers as the base, and conduct some sort of if, index, match combo to extract the data I need.

    The problem I am running into occurs when there are duplicated serial numbers in the raw data table. I believe if I add an additional criteria that returns the data based on whether the destination is a retail store or WHS, I should be able to fix this issue. With that said, I haven't been able to figure out how to fix the problem on my own

    I have attached a sample data file with the current index, match formula I am using.
    Attached Files Attached Files

  2. #2
    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
    79,406

    Re: Returning unique values based on duplicate criteria

    You might want to go back to your first thread here and thank the person who saved you and mark the thread as solved.

    Which latest version do you have? Update your forum profile with either MS365 or Office 2019.
    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.

  3. #3
    Registered User
    Join Date
    04-14-2021
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Returning unique values based on duplicate criteria

    Thanks for the heads up, Ali. I marked the tread as solved and dished out some much deserved reputation points

    I have MS 365 (updated on profile now).

  4. #4
    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
    79,406

    Re: Returning unique values based on duplicate criteria

    Thanks - don’t forget to sign off threads, though.

  5. #5
    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
    79,406

    Re: Returning unique values based on duplicate criteria

    I don’t understand your sample data: where are the ID numbers coming from? I think you need to explain in more detail what you are trying to achieve here.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Returning unique values based on duplicate criteria

    I have taken an "old fashioned" approach of a helper column:

    in F3

    =A3&"-"&COUNTIF($A$3:$A3,$A3)

    and then

    in B22

    =INDEX(B$3:B$16,MATCH($A22&"-" & COUNTIF($A$22:$A22,$A22),$F$3:$F$16,0))

    copy across and down

    If the starting point of the Analysis table is the Serial number then to use the Destination as part of the "key" requires a conditional IF like) test.

    I know 365 provides much more functionality so perhaps TEXTJOIN / UNIQUE combination?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-14-2021
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Returning unique values based on duplicate criteria

    Oh that's awesome.

    I was having a heck of a difficult time trying to even explain my problem more clearly! Thanks for giving me an approach to solving it.

    It would be nice to be able to do this without having to create a new column of information, but I will take your advice and look into Textjoin / Unique combo's to see if it can fix my issue through those methods.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Returning unique values based on duplicate criteria

    You're welcome. I hope you find a more "sophisticated" 365. I recognise having "helper" columns for large data volumes is not ideal; however they often offer a simple but effective solution.

+ 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. Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values
    By johnwilliamboyle in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-05-2019, 12:09 PM
  2. Replies: 8
    Last Post: 05-08-2019, 11:38 AM
  3. [SOLVED] FREQUENCY of unique value and SUM of unique values based on criteria
    By dcathey5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-05-2017, 11:02 AM
  4. List unique values matching a criteria that has duplicate values
    By Andrewjs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 10:11 PM
  5. [SOLVED] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  6. Replies: 2
    Last Post: 02-06-2013, 04:44 AM
  7. Replies: 6
    Last Post: 02-07-2012, 08:21 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