Is there a way for me to use a function to create a sorted version of data in an array? That is, I have a bunch of data which is being calculated and displayed in tabular form. Is there a way to display, somewhere on the same worksheet, a “sorted” version of the same list, without having to manually sort the cells (potentially screwing up references elsewhere)? The closest thing that I found was the “Rank” function, but it doesn’t really do what I want.
Have you tried a pivot table?
The SMALL function will work if the sort column is a number. Lining up adjacent columns in the target section of your sheet will be fun. You’ll need the VLOOKUP for that.
I think that this is a case where recording a macro is going to be the simplest solution.
A pivot table will probably do what you need, however it does not update automatically as you change values…
You can write a macro to re-define the pivot table on demand. VLOOKUP could also be used for this, however it may be more complicated.
Thank you all - I was able to use SMALL and VLOOKUP to basically get what I want (it doesn’t deal well with ties, but I can probably cope with that).
My next question - is there any way to take a large array of text values with many repeats and return a list of the unique words only?
I’ll not ask the same question again, but instead refer you to my previous post.
Filtering for a unique value is actually pretty easy.
What you need is to ensure that your column has a column name on it. Excel gets fussy if the name is not there.
Highlight the values that you want to filter, and click on the toolbar button [DATA], then [FILTER] and choose [ADVANCED FILTER].
This gives you one action and three options. First, where do you want the results to show [ACTION]. Personally, I usually choose a different column so I can see the before & after lists.
The [CRITIERIA RANGE] should be filled in with the positions of the values you’ve chosen. The criteria range would only be used ig you are looking for a specific value(s). For a unique scan, you can leave it empty.
If you determined that you want the results in a different column, the [COPY TO] box will be visible. Choose where you want to put the output on your spreadsheet.
Then click [UNIQUE RECORDS ONLY], and voila! You’ve got your unique list of values.
Hope this helps!!
Eliphalet
KneadToKnow, I have not used a PivotTable, because I have not been able to figure out how it would help. I’m not very familiar with them, and the Excel help dialogs on this topic are not very helpful. If you think it’s possible to get a PivotTable that will automatically sort and filter unique values for me, I’ll try to slog through to understand them. But if it’s not going to do me any good at the end of the day, then I’d just as soon not bother.
Eliphalet, thanks. I think I can adapt this to a macro that will work for what I want to do. (It’s a little more complicated because I don’t just have a single list that I’m trying to find non-duplicates for, but a 2-d array where I want the non-duplicates not by rows, but by individual cells).