• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove a product list from selection

mckhoa

New Member
I have a list i.e.


List 1

Apple

Pear

Oranges


Challenge:


I would like to have a drop down box select a product from List 1 (Easily done)


However, in another column, it would populate all but the selected item from List 1.


The challenge is to do this without VB code. (And also the list could possibly contain over 30 items.)


Thanks for any help you can provide.


Results


Selection: Apple


List 1 List 2

Apple Orange

Orange Pear

Pear
 
Assuming List1 is in A2:A4, dropdown is in B2, List2 starts in C2


Formula in C2:

=IF(COUNTIF($A$2:$A$4,"<>"&$B$2)<ROWS(C$2:C2),"",INDEX(A:A, SMALL( IF($A$2:$A$4 <>$B$2, ROW( $A$2:$A$4)), ROW(A1))))

Use Ctrl+Shift+Enter to confirm, copy down as far as would ever be needed.


How it works:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
When I do this it gives me all Apple when I select Orange or Pear, it does not give me what remains in the list. Does that make sense?


List1

Apple

Pear

Bannana


I select "Apple"


Output list should be:

Pear

Bannana


Does that make sense?
 
Are you sure you confirmed formula properly? It's Ctrl+Shift+Enter, not just Enter. If done correctly, formula will look like this in formula bar:

{=IF(COUNTIF($A$2:$A$4,"<>"&$B$2)<ROWS(C$2:C2),"",INDEX(A:A, SMALL( IF($A$2:$A$4 <>$B$2, ROW( $A$2:$A$4)), ROW(A1))))}
 
Yes, I made sure to do ctrl+shift+enter


the results:

List

Apple

Pear

Bannana


selection

Apple


Results

Pear

Pear


Test#2

List

Apple

Pear

Bannana


selection

Pear


Results

Apple

Apple


Third test:

List

Apple

Pear

Bannana


selection

Bannana


Results

Apple

Apple
 
I'm not able to duplicate your error. Are you copying formula exactly? Calculation mode is Automatic? No strange macros running in background? Formula was confirmed in C2 and then copied to other cell, not confirmed in all cells at same time?
 
There isn't any error. I am copying and pasting your code. Calculation is set to Automatic and I have a brand new no macros running. We might have a communication error.


I just want to make sure I am communicating this correctly.


I have a list (List 1) of Products containing the following;

Apple

Pear

Banana


I will then select "Apple" in my dropdown list


the column next to it should product a list that contains the following only;

Pear

Banana


if I was to select "Pear" it should provide only;

Apple

Banana


if I was to select "Banana" it should provide only;

Apple

Pear


Thanks for all your help.
 
I think I understand you...just don't know why it's not working. This is what I see on my workbook

[pre]
Code:
List1	Dropdown	List2
apple	apple	        pear
pear		       banana
banana
[/pre]
The only way I got a result like yours was by selecting C2:C4 and THEN inputting the formula. You may need to delete all the formulas from column C, then select only C2, and input the formula.
 
When referring to Luke, you must refer to him as Luke, the God of excel. I have seen him get a little upset when he is not properly addressed. Just fyi ;)
 
lol @ Montrey.


@mckhoa

Glad to hear you got it working. I'm assuming that my guess about the array being confirmed in multiple cells was the issue?
 
Good day

Contextures is a site with many fine illustrated work through's, you may find this link helpful I learnt a lot about drop downs and dependents from this site.


http://www.contextures.com/xlDataVal03.html
 
Back
Top