## Create a list of distinct values from a list where an adjacent cell value meets a criteria

*Article updated on August 28, 2017*

**Question:** How do I create a distinct list from column C (Values) where cell values in column B (Category) equals the value AA? See picture below.

**Answer:**

**Array formula in cell H2:**

If you don't want to use an array formula, read this post:

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

**How to create an array formula**

- Copy above array formula
- Select cell H2
- Click in formula bar
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

**How to copy array formula**

- Select cell H2
- Copy cell (Ctrl +c)
- Select cell H3
- Paste (Ctrl + v)

**Download excel example file.**

create-a-list-of-distinct-values-where-adjacent-cell-value-meets-criteria11.xls

(Excel 97-2003 Workbook *.xls)

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]### 15 Responses to “Create a list of distinct values from a list where an adjacent cell value meets a criteria”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How do I do this with 2 criteria?

See this blog post: https://www.get-digital-help.com/2009/06/27/how-to-create-a-unique-distinct-list-where-other-columns-meet-two-criteria/

Hi Oscar,

I am trying to group similar cells together and add up duplicates. That is I have baseline data, and then in the follow up, i have a few that are missing, and I am trying to group them all together. To illustrate:

HHID Income

6001 1000

6002 3000

6003 3000

6004 3000

6005 2000

6006 5000

Now I need to calculate total HH Income by deducting the costs involved but the dataset is like this due to different plots in crop production.

HHID Cost of Production

6001 2000

6001 3000

6003 500

6006 200

now i need to compare or rather use only the ones that are available first by adding the repeated ones as one and then grouping on the available HHIDS, but since the dataset is large i cant do it manually, i can do it easily in stata, but i am yet to renew my licence, can i do this in excel, if yes, can u tell me the formula?

I am most obliged.

Regards,

Valentine

Valentine Gandhi,

Check out this file:

https://www.get-digital-help.com/wp-content/uploads/2009/05/Gandhi.xls

Hi,

I have problem replicating the same formulae.I did the exact same formulae.

It returns with #NA although it returns with the correct number of rows in the distinct list. For one criteria, the distinct list looks up with the same value in multiple rows which is not right.

My formulaes below:

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$173, IF(MATCH($B$2:$B$173&$C$2:$C$173, $B$2:$B$173&$C$2:$C$173, 0)=ROW($B$2:$B$173)-1, 1, ""), "")), "", SMALL(IF($F$1=$B$2:$B$173, IF(MATCH($B$2:$B$173&$C$2:$C$173, $B$2:$B$173&$C$2:$C$173, 0)=ROW()-1, ROW()-1, ""), ""), ROW()-1))

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$173, IF(MATCH($B$2:$B$173&$C$2:$C$173, $B$2:$B$173&$C$2:$C$173, 0)=ROW($B$2:$B$173)-1, 1, ""), "")), "", INDEX($B$2:$B$173, $H$2:$H$173))

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$173, IF(MATCH($B$2:$B$173&$C$2:$C$173, $B$2:$B$173&$C$2:$C$173, 0)=ROW($B$2:$B$173)-1, 1, ""), "")), "", INDEX($C$2:$C$173, $H$2:$H$173))

I am using excel 2010.

I cant upload my excel spreadsheet. It will be easier if I can do that. Please help!

Thanks.

Miss Lee,

I have removed 2 formulas and the remaining array formula is simplified. I hope this makes it a lot easier?

Remember to adjust cell reference (bolded):

=INDEX($C$2:$C$16, MATCH(0, IF($F$1=$B$2:$B$16, COUNTIF(

$H$1:$H1, $C$2:$C$16), ""), 0))Hi Oscar,

This is very useful, however can you please tell me how do I get the same unique values using regular formula. I don't want with {}

Hi,

I dont really understand the match formulae. What is the cell reference referring to?

Let me simplify with a model.

I have these 2 columns of data. Column B (Country) and Column C (Parent Branch).

Country Parent Branch

Korea a

Korea b

Korea c

Korea d

Korea e

Korea f

Korea g

Korea h

Korea i

Korea q

Japan s

Japan d

Japan j

Japan fd

Japan fg

Japan vc

Japan x

Japan cx

Japan cx

China xcc

China vcx

China vxc

China cx

I have a cell (F1) which I can select either Korea, Japan and China.

So I want to be able to lookup to this cell and then generate another listing with only the selected county and the related parent branch will show.

So if F1 is Korea,

the 2 columns I want to generate will show just

Korea a

Korea b

Korea c

Korea d

Korea e

Korea f

Korea g

Korea h

Korea i

Korea q

Hope this is clearer.

Thanks in advance for you help.

Miss Lee,

I used an array formula from this post:

How to return multiple values using vlookup

Array formula in cell D5:

Download excel *.xlsx file

Miss-Lee.xlsx

Hi Oscar,

That is very useful! Good stuff!

Just one more question to clarify, what do I need to change if my two columns are not column A and B?

I tried to replicate the formulae when my 2 columns of data are in column I and J; it does not work even after i change the row and column reference to I1 and J1.

Thanks!!

Miss Lee,

I don´t know your exact cell references, but your aray formula could look like this:

=IFERROR(INDEX($I$2:$J$24, SMALL(IF($E$2=$I$2:$I$24, MATCH(ROW($I$2:$I$24), ROW($I$2:$I$24)), ""), ROW(A1)), COLUMN(A1)), "")

Remember to enter this formula as an array formula.

[...] needs to be copied down. You can find very useful information for some of these formulas here... Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel ... I hope that helps. [...]

Hi Oscar,

This is very useful, however can you please tell me how do I get the same unique values using regular formula. I don't want a array formula.

Thanks a ton,

Nandish

Hi Oscar

Incredible code!

I needed pretty much your exact original post except for 1 thing. I don't want it to just allow 1 of the numbers. So in your original example, column "H" would show 4,2,2,4 as opposed to just 4,2.

Is that possible?

Thanks a lot

Wow!

Very useful for me!!

Thanks