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

**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:**

**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

**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)

### Category: Unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

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

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Comments(40) Filed in category: Excel, Unique distinct values

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Comments(31) Filed in category: Excel, Unique distinct values

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

Comments(24) Filed in category: Excel, Sorted unique distinct values

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains […]

Comments(24) Filed in category: Excel, Unique distinct values

### 15 Responses to “Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel”

### Leave a Reply

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

<code>your formula</code>

**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