Hi excel gurus,
I am working on a big data file relating to exam results. approximately 50000 rows. I want a drop down menu or any other excel option to summarize the data into location wise. Attach excel sheet column B consists of location. I need a option in the same sheet when I click UK all data need to appear. When I sent this data location rep like to see there data only. Pivot table I tried. Please help me on this.
What's wrong with using a data filter?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glen,
The end users are not familiar with excel. That's why like a option where u click "UK" then get al the stats. Please can you help me on this
On 50,000 rows, this might be slow!!
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$11=Sheet2!$A$1,ROW(Sheet1!$B$2:$B$11)),ROWS(A$3:A3))),"")
... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Thanks Glen it works
You're welcome and thanks for the Rep.
You can also use this formulaFormula:
=IFERROR(INDEX(Sheet1!A$2:A$5000,AGGREGATE(15,6,ROW($1:$5000)/(Sheet1!$B$2:$B$5000=$A$1),ROWS($3:3))),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks