Filter-sort formula results?

**martix** · 11-19-2018, 05:06 AM

I have a large table of generated results.

When I use the basic filter sorting doesn't work. Which is probably due to the relative nature of the formulas.

See the attached workbook.

Is there a way to make the sorting work?

...while also not making the workbook a nightmare to maintain?
Because, when you make the cells absolute references sorting starts working again. But it makes the table a nightmare to change and maintain. I don't know of a way to bulk-change the references of a large area of cells(a regex replace function would be so handy here). But the fill function stops working then. And excel doesn't even have a "Find/replace in selection".
Keep in mind that I CANNOT touch the source.

**José Augusto** · 11-19-2018, 05:34 AM

Hi

I do not know if this helps you but I try

Use a helper column with the address of each cell of source and the copy of source is given by INDIRECT function

Suppose your Source in F3:F6

Use in A2:A5 the values {"F3","F4","F5","F6"}

use in B2:B5 the formula =INDIRECT(A2) (drag down)

Sort A2:B5 on column B to get the sorted source or on A to get the original source in B

See the file for clarification

**martix** · 11-19-2018, 10:39 AM

This breaks down if you move column A.

But I have had an epiphany!

Text below used for rubber-duck debugging.
(Disclaimer: explanation somewhat mathy.)

See, you have to have some absolute value somewhere in the table.
That may be an absolute cell reference or a primitive value, it doesn't matter.

The trick then, is to tie your formula to that absolute value.

You see, even with relative references, excel DOES sort the table. Problem is, once sorted, the formulas update, and all that is relative returns to its old values(if you combine formulas with both you might get some really weird results and hard to track down bugs).

The solution is simple once you realize what the problem is, and it involves a helper column you might want in your table anyway - an ordinal column(you might want it, because excel has no "turn off sort" functionality - once you sort a table, you can't go back, unless the original ordering is also a valid sort of your table).
So you don't use relative references, you use OFFSET with ROW and COLUMN. This nets you at most 3 parameters(down to 1 in the ideal case) - a reference point to your source, and potentially translational offsets for your ROW/COLUMN(which you can avoid by having your reference point to the top left corner of the source).

This makes it both extensible(you can copy the formula over and it will work without having to change it) and maintainable(if you need to change it, you only need to worry about at most 3 parameters, not 3000 cells you need to manually change).

So... do I get a cookie for solving my own problem?

**José Augusto** · 11-19-2018, 01:51 PM

Hi

I'm glad you solved your problem. I think your cake already got it.

Note however that you are solving a problem that you had not placed.

Originally Posted by martix

I have a large table of generated results. ...
Keep in mind that I CANNOT touch the source.

Do not forget to mark the thread as solved.

Regards

**martix** · 11-19-2018, 11:23 PM

Originally Posted by José Augusto

Note however that you are solving a problem that you had not placed.

Not sure what that's supposed to mean...
I haven't touched the source data at all in what I described.

You may have misunderstood the problem.
We have 2 tables - table A with generated results we wanna filter, and another table B where the source data for table A lives.
We take data from table B and do things with it, to make it presentable and put it in A. But we cannot modify B.

**José Augusto** · 11-20-2018, 08:27 AM

Hi

The solution I proposed uses the table 'A1:B5' and not individual columns or individual cells. You can change the table to where you want to be without any kind of problem, adjusting the references in case you opt for another sheet.

When you sort, you must do this for the table and not for a column isolated from the other. You can see at post #2 where I say that

Sort A2:B5 on column B to get the sorted source or ...

**martix** · 11-20-2018, 02:46 PM

Seriously, what are you talking about?
What does this have to do with the statement I quoted in my last reply?

Filter-sort formula results?

LinkBack

Thread Tools

Rate This Thread

Display

Filter-sort formula results?

Re: Filter-sort formula results?

Re: Filter-sort formula results?

Re: Filter-sort formula results?

Re: Filter-sort formula results?

Re: Filter-sort formula results?

Re: Filter-sort formula results?

Thread Information

Users Browsing this Thread

Similar Threads

Filter using a formula to return results on different worksheet

cant sort table differently without changing formula results

[SOLVED] creating a formula to sort names from one column's results and sort them based on another

Formula to filter results from data

Sort array formula results in descending order

Excel 2007 : Need to filter results, add results and group the results in Excel 2007

SORT RESULTS OF A FORMULA (this should be very simple)

Bookmarks

Bookmarks

Posting Permissions