• 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.

How to extract a list of items

saints49

New Member
I have lots of DVDs which I want to catalogue them using Excel 2010. I have 3 sheets. In Sheet 3, there are 2 lists: Category (PG, M18, General, Adult) and Language (English, Korean, Japanese, etc). Sheet 2 contains data such as Titles, Category, Language, etc. I use Sheet 1 to create a drop down list for selecting a Title and then displaying information of the selected DVD. (I use vlookup formula and it works fine.)
Then I created another drop down list for Category. I want to be able to select a category, such as M18, and then a list of DVD titles that fall under this category will be displayed. However I failed to get the display as there are something wrong with the formula I used.
I have attached the file so that it will be clearer to see my problem. In the file, you will see that I have tried 2 different formulae but they didn't work.
I hope someone will assist me in getting the formula correct. Thank you.
 

Attachments

  • Sorting Test - Example.xlsm
    29.5 KB · Views: 6
Hi there-

Would something like this work?

=IFERROR(INDEX(Sheet2!$A$6:$A$27,SMALL(IF(Sheet2!$C$6:$C$27=Sheet1!$A$11,ROW(Sheet2!$C$6:$C$27)-ROW($C$6)+1),ROWS(Sheet2!$C$6:$C6))),""). This should be entered as an array formula Ctrl+Shift+ Ent), and dragged down as far as necessary. Adjust the ranges where necessary.

One thing I also noticed, is that some of your "categories" don't match what is in your data set (ie. "adult" vs "adult / R21"). So you may have to update your drop down list to get a match.
 

Attachments

  • Sorting Test - Example_3G.xlsm
    33.5 KB · Views: 3
I find using
=IF(ROW(F7)>=COUNTIF(Sheet2!$C$6:$C$27,$A$11)+7,"",(INDEX(Sheet2!$A$6:$A$27,small(if((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5)>0,((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5),max(((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5))+1),ROW(C1)))))

entered in F7 with Ctrl+shift + enter and copied down works
 
Hi there-

Would something like this work?

=IFERROR(INDEX(Sheet2!$A$6:$A$27,SMALL(IF(Sheet2!$C$6:$C$27=Sheet1!$A$11,ROW(Sheet2!$C$6:$C$27)-ROW($C$6)+1),ROWS(Sheet2!$C$6:$C6))),""). This should be entered as an array formula Ctrl+Shift+ Ent), and dragged down as far as necessary. Adjust the ranges where necessary.

One thing I also noticed, is that some of your "categories" don't match what is in your data set (ie. "adult" vs "adult / R21"). So you may have to update your drop down list to get a match.

Thanks 3G. It works well. Thanks for letting me know about the mismatch data. I have made the update.
 
I find using
=IF(ROW(F7)>=COUNTIF(Sheet2!$C$6:$C$27,$A$11)+7,"",(INDEX(Sheet2!$A$6:$A$27,small(if((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5)>0,((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5),max(((Sheet2!$C$6:$C$27=$A$11)*(ROW(Sheet2!$C$6:$C$27)-5))+1),ROW(C1)))))

entered in F7 with Ctrl+shift + enter and copied down works

Thanks Jake for your help. Your formula works and it's true that the listing of titles are in the reverse.
 
saints49 welcome to the forum

Have you tried a pivot table?

If you quote all reply's your thread is going to get unnecessarily long and tedious to read, people general just quote parts of a post the high light a point.

please look at the upload, why is your workbook saves as xslm when there is no code.

I have named the tab!
 

Attachments

  • Sorting Test - Pivot table.xlsm
    36.2 KB · Views: 10
Back
Top