# Creating a list of unique values based on multiple criteria

1. ## 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.

I feel I may not explain this sufficiently in text.

2. ## 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:
`Please Login or Register  to view this content.`

then

Formula:
`Please Login or Register  to view this content.`

#2

Formula:
`Please Login or Register  to view this content.`

note: have assumed ; delimiter

3. ## 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. ## Re: Creating a list of unique values based on multiple criteria

@XLent & @Pete_UK

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

5. ## 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. ## Re: Creating a list of unique values based on multiple criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)