# Creating a list of items selected "yes" without duplicates

1. ## Creating a list of items selected "yes" without duplicates

Hi there guys,

I have attached a sample of what im trying to do here.

A2:A10 i have items, B2:B10 they can selected "yes" or "no" and C2:C10 the values

what i would like is, is "yes" was selected, i would like the item name added to a list. so basically:
=IF(A2:A10="YES",A2:A10)

and then i can just add the value to each item later (that part im fine with)

but i need to remove duplicates and obv have this carried throughout the cells.

3. ## Re: Creating a list of items selected "yes" without duplicates

Try this...

Data Range
 A B C D E F G 1 ----- ----- ----- ----- ----- ----- ----- 2 Item Yes/No Value Yes Item Value 3 Item 1 yes 1 Item 1 1 4 Item 2 no 2 Item 4 4 5 Item 3 no 2 Item 10 1 6 Item 4 yes 3 7 Item 4 yes 1 8 Item 6 no 1 9 Item 7 no 1 10 Item 8 no 1 11 Item 9 no 2 12 Item 10 yes 1 13

This array formula** entered in F3:

=IFERROR(INDEX(A\$3:A\$12,MATCH(0,IF(B\$3:B\$12=E\$2,COUNTIF(F\$2:F2,A\$3:A\$12)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in G3:

=IF(F3="","",SUMIF(A\$3:A\$12,F3,C\$3:C\$12))

Select F3:G3 and copy down until you get blanks.

4. ## Re: Creating a list of items selected "yes" without duplicates

You can use this formula

=IFERROR(INDEX(\$A\$3:\$A\$12,SMALL(IF(\$B\$3:\$B\$12="yes",ROW(\$B\$3:\$B\$12)-2,""),ROWS(\$A\$1:A1))),"")

In C3 and drag it down

**Array formulas must be entered with Ctrl+Shift+Enter key combination.

 A B C 1 2 Item Yes/No Value 3 Item 1 yes Item 1 4 Item 2 no Item 4 5 Item 3 no Item 5 6 Item 4 yes Item 10 7 Item 5 yes 8 Item 6 no 9 Item 7 no 10 Item 8 no 11 Item 9 no 12 Item 10 yes

5. ## Re: Creating a list of items selected "yes" without duplicates

Originally Posted by AlKey
You can use this formula

=IFERROR(INDEX(\$A\$3:\$A\$12,SMALL(IF(\$B\$3:\$B\$12="yes",ROW(\$B\$3:\$B\$12)-2,""),ROWS(\$A\$1:A1))),"")
That won't exclude duplicates:

i need to remove duplicates

6. ## Re: Creating a list of items selected "yes" without duplicates

Hi Tony,

Your formula works to an extent, my problem is that my cells run accross no down (C3:C6 is actually C3:L3)
does the code change then?

Thanks otherwise everyone for the fast response

7. ## Re: Creating a list of items selected "yes" without duplicates

If your actual data goes across rows then why didn't you do it that way in your sample file!

How about posting a new sample file that accurately demonstrates your data structure.

Also, tell/show us where you want the results to appear.

8. ## Re: Creating a list of items selected "yes" without duplicates

Hay so sorry, i changed it and i wasnt aware that it would play so much on the formula,

in case you wondering why it runs across, there will be % graphs going above it. (sorry i cant post the actual file as it is sensitive company info)

Self teaching yourself excel can easily drive you crazy!!

Thanks again Tony

9. ## Re: Creating a list of items selected "yes" without duplicates

Ok, so want the results to go across a row...

No problem, just need a minor tweak.

Data Range
 A B C D E 1 ----- ----- ----- ----- ----- 2 Item Yes/No Value 3 Item 1 yes 1 4 Item 2 no 2 5 Item 3 no 2 6 Item 4 yes 3 7 Item 4 yes 1 8 Item 6 no 1 9 Item 7 no 1 10 Item 8 no 1 11 Item 10 yes 2 12 Item 10 yes 1 13 14 15 Yes 16 Item Item 1 Item 4 Item 10 17 Value 1 4 3

This array formula** entered in B16:

=IFERROR(INDEX(\$A3:\$A12,MATCH(0,IF(\$B3:\$B12=\$A15,COUNTIF(\$A16:A16,\$A3:\$A12)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in B17:

=IF(B16="","",SUMIF(\$A3:\$A12,B16,\$C3:\$C12))

Select B16:B17 and copy across until you get blanks.

10. ## Re: Creating a list of items selected "yes" without duplicates

Hi,

i figured the tweaks would be just that, moving the \$, but now it carries duplicates for some reason?? Any idea why?

I have removed all details and just added the sheet so you can actually see what im busy with. Cause i know how confusing i can be when trying to explain something.

Thanks again

11. ## Re: Creating a list of items selected "yes" without duplicates

Ugh! Merged cells!

Unmerge the cells from C2:C7.

Then, enter this array formula** in C2:

=IFERROR(INDEX(\$N2:\$N33,MATCH(0,IF(\$O2:\$O33=\$B18,COUNTIF(\$B2:B2,\$N2:\$N33)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to L2

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1