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

### 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: http://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:

http://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