Sorting a large table by a drop-down list

[Navac]

Hi all,

I have a question about sorting that I have not been able to find the answer to. Using Excel 2010

My problem: I would like to sort a large table ('Participant Table'!A1:AG47638) based on a value of around 50 different values in drop down list ('Choose your Location'!G7), which matches values in 'Participant Table'!I:I

I have been successful using a third sheet to copy the people I need using and array index formula, but due to the size it takes a LONG time to compute, and is not useful:

=IFERROR(INDEX('Participant Table'!D$2:D$47638,SMALL(IF('Choose your Location'!$G$7='Participant Table'!$J$2:$J$47638,ROW('Participant Table'!$J$2:$J$47638)-MIN(ROW('Participant Table'!$J$2:$J$47638))+1,""),ROW('Participant Table'!A1))),"")


Does anybody have a solution that would require less resources? I think vba is the route to go but I've never used it so I'm ignorant in it's use very beginner solution here would be great - I would love to learn it myself but unfortunately don't have the time to fix this problem.

Thank you SO much in advance.

Cavan

 

[baitmaster]

Do you mean Filter instead of sort? Filter = show only data that meets a criteria, Sort = arrange whole table alphabetically

And is your data in an actual Excel "table" object, or just a range of data that you have created?

If I interpret this correctly you can achieve this by using the AutoFilter property of the Excel Table, then simply copy and paste the visible values. You can do this via code or manually, its not too difficult. Is this what you want?

 

[Navac]

I'm sorry, I may not have been clear.

Filter is what I meant yes. I could do either a Table or range of data, I'm familiar with both. Currently it's a filtered range.

What I'm trying to create is an automated tool that automatically filters from a value selected on a drop-down list by another user. I would like to make it as easily as possible as I don't know their Excel skill level. I would like to filter out all cases that do not match the value selected in the drop-down.

 

[Navac]

Hi all, I was able to solve my problem with this macro for all interested, I also included a button-click to begin the macro.

vba - Excel Return Whole row when it Matches Cell - Stack Overflow

 

[SpillerBD]

For 2013, "slicers" are an available option for TABLES. A much easier way to add visual filtering of a Table.
Slicers are new and would run into compatibility issues if sending file to user's of prior versions.

 

[Navac]

Thanks, for the advice I'll look into that on my personal machine. My organization has 2010 Excel.

 

[baitmaster]

Glad you got it fixed. The approach I use for this task is a combination of macro, SEARCH formula and autofilter. Essentially you add a column to your data table containing a SEARCH formula to see if any part of the text contains your search string, then you filter on that column of the table for TRUE or FALSE. The filter is triggered by a worksheet_change event, so no buttons required

The advantage of this approach comes in terms of efficiency, the code you linked to will be slow on large complicated models unless you adapt it to hold results in an array before dumping the whole lot onto a wprksheet in one go. The other advantage is that it is self-contained, you don't need to work with another worksheet and this can become a standalone data-search tool.

But this is VBA there is no right or wrong answer

 

[Navac]

A worksheet_change even would be really what I was interested in, but given the time constraints and my unfamiliarity with VBA I just did what I could find/understand. I will certainly look to try the method you suggest in the future!