+ Reply to Thread
Results 1 to 6 of 6

Creating a list of unique values based on multiple criteria

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Question Creating a list of unique values based on multiple criteria

    Hi,

    I'll try to keep this as short and concise as possible.

    I'd like to create a list of unique values extracted from another raw-data list.
    See attached sheet for reference. Here's a picture of it:
    2019-03-13 10_01_14-Unique List on multiple criteria.xlsx - Saved.png


    1. I'd like to remove duplicates that have both values from col-C and col-E repeated in rows. (No duplicates of Supplier+Parts).

    2. Optional Bonus task (Yellow cells): I'd like to add col-G and col-H that looks up Supplier&Part and gives the value of col-B based on col-A as a criteria. Looking at the "What I want" table makes more sense than words IMO.
    I tried this with COUNTIFS formula, and VLOOKUP. I didn't manage that with the 2+1 criteria in this case.


    Please ask if anything is unclear.
    I feel I may not explain this sufficiently in text.
    Attached Files Attached Files
    Last edited by 27POP27; 03-13-2019 at 07:58 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Creating a list of unique values based on multiple criteria

    #1

    to keep things simple - calculate the row position once and reference in the return cells

    B18:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    #2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    note: have assumed ; delimiter

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Creating a list of unique values based on multiple criteria

    You can put this formula in cell G3 of your sample file:

    =IF(COUNTIFS(D$3:D3,D3,E$3:E3,E3)=1,MAX(G$2:G2)+1,"-")

    then copy down to G11. Note that you may have to change the commas ( , ) to semicolons ( ; ), depending on your regional settings.

    Then you can use this formula in cell C18:

    =IFERROR(INDEX(C$3:C$11,MATCH(ROWS($1:1),$G$2:$G$11,0)),"")

    Again, change the commas ( , ) to semicolons ( ; ) if required. Then you can copy across to F18, and copy these down as far as you need to (until you start to get blanks).

    You can use this array* formula in G18:

    =IFERROR(INDEX($B$3:$B$11,MATCH(1,($D$3:$D$11=$D18)*($E$3:$E$11=$E18)*($A$3:$A$11=LEFT(G$17,3)),0)),"None")

    *Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

    Copy this into H18, then copy down as required. I get slightly different answers to the ones you show.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Creating a list of unique values based on multiple criteria

    @XLent & @Pete_UK

    Thank you for your responses.
    I've found that Pete_UK's solution is the most compact and cleanest, but it skips one part from ESP European Steel Production. (ESP has two different parts. This formula may not have taken that into consideration).

    Pete_UK's solution provides the exact solution I'd like, with an extra column.
    2019-03-13 12_55_03-Unique List on multiple criteria (2).xlsx - Saved.png
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Creating a list of unique values based on multiple criteria

    Thanks for the rep.

    My second formula had a slight mistake in it - it should be:

    =IFERROR(INDEX(C$3:C$11,MATCH(ROWS($1:1),$G$3:$G$11,0)),"")

    in C36 (in your latest file), then copy this across and down.

    Hope this helps.

    Pete

  6. #6
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

    Re: Creating a list of unique values based on multiple criteria

    Try
    Please Login or Register  to view this content.
    Fill right and down.

+ 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. [SOLVED] Creating a list of unique values based off criteria
    By kristentringali in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-23-2018, 12:54 PM
  2. Creating Unique List based on specific criteria
    By Dan79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2017, 09:37 AM
  3. [SOLVED] Creating unique list of values from multiple columns not in sequence.
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-09-2017, 04:47 AM
  4. [SOLVED] VBA Creating a list of unique values from one column based on criteria from another column
    By bilbo85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2016, 02:38 PM
  5. [SOLVED] List unique values based on multiple criteria
    By tangmere.milli in forum Excel General
    Replies: 6
    Last Post: 01-22-2016, 07:56 AM
  6. List Unique Values Based On Criteria
    By tangmere.milli in forum Excel General
    Replies: 5
    Last Post: 03-16-2015, 09:49 AM
  7. Replies: 3
    Last Post: 07-08-2014, 03:10 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