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

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

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)

5 easy ways to extract unique distinct values

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

Extract a unique distinct list from two columns

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

Create a unique distinct alphabetically sorted list

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

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 […]

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 a unique distinct list from three columns

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

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 a unique distinct list and remove blanks

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

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

How to create a unique distinct list where other columns meet two criteria

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

### 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 comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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