Custom-sort filter array table

F

freddyt

New Member
Joined
Apr 13, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have a table on an audits tab created using the filter() formula so that it pulls data from a master list sheet. I want to be able to sort the data on the audits sheet without using formulas, instead using the common filter dropdown. I've tried to do this and get the error " can't change part of an array", so I'm looking for a way to achieve this purpose. I tried advanced filter and VBA so that whenever the master list changes, I can refresh the audits sheet to reflect the changes but that doesn't work either. Is there a way to do this and still be able to sort the audits sheet results?
 

Attachments

  • Master List.png
    Master List.png
    73.9 KB · Views: 13
  • Filter Results.png
    Filter Results.png
    88 KB · Views: 12

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
The way I addressed this issue in the past was to have a drop-down box near the table for all of the column headers that you might sort by, and then use the sorting functions and IF based on the pick-from-a-list.

I'd love to see how others might have dealt with this.
 
Upvote 0
JamesCanale said:
The way I addressed this issue in the past was to have a drop-down box near the table for all of the column headers that you might sort by, and then use the sorting functions and IF based on the pick-from-a-list.

I'd love to see how others might have dealt with this.
Click to expand...
I'm thinking along the same lines, but I'm not sure how to include the sort and IF functions.
 
Upvote 0
Here is some nonsense that I made up.
MrExcelPlayground16.xlsx
ABCDEFGH
1NameExit DateReasonNameNameExit DateReason
2John1/1/2023Fired for Incompentence1/10/2023Fred1/10/2023Quiet Quit
3John1/2/2023Smells2/15/2023Fred1/11/2023Fired for Incompentence
4John1/3/2023RetiredFred1/12/2023Smells
5John1/4/2023Better JobFred1/13/2023Better Job
6John1/5/2023Own BusinessFred1/14/2023Better Job
7John1/6/2023SmellsFred2/7/2023Fired for Incompentence
8Fred1/7/2023RetiredFred2/8/2023Smells
9Fred1/8/2023Better JobFred2/9/2023Retired
10Fred1/9/2023Own BusinessFred2/10/2023Better Job
11Fred1/10/2023Quiet QuitFred2/11/2023Own Business
12Fred1/11/2023Fired for IncompentenceFred2/12/2023Quiet Quit
13Fred1/12/2023SmellsFred2/13/2023Smells
14Fred1/13/2023Better JobGeorge1/27/2023Better Job
15Fred1/14/2023Better JobGeorge1/28/2023Retired
16Harry1/15/2023Own BusinessGeorge1/29/2023Better Job
17Harry1/16/2023Quiet QuitGeorge1/30/2023Own Business
18Harry1/17/2023Fired for IncompentenceGeorge1/31/2023Quiet Quit
19Harry1/18/2023SmellsGeorge2/1/2023Fired for Incompentence
20Harry1/19/2023Complete JerkGeorge2/2/2023Smells
21Harry1/20/2023Better JobGeorge2/3/2023Retired
22Harry1/21/2023Own BusinessGeorge2/4/2023Better Job
23Harry1/22/2023Quiet QuitGeorge2/5/2023Own Business
24Harry1/23/2023Fired for IncompentenceGeorge2/6/2023Quiet Quit
25Harry1/24/2023SmellsHarry1/15/2023Own Business
26Harry1/25/2023Quiet QuitHarry1/16/2023Quiet Quit
27Harry1/26/2023Better JobHarry1/17/2023Fired for Incompentence
28George1/27/2023Better JobHarry1/18/2023Smells
29George1/28/2023RetiredHarry1/19/2023Complete Jerk
30George1/29/2023Better JobHarry1/20/2023Better Job
31George1/30/2023Own BusinessHarry1/21/2023Own Business
32George1/31/2023Quiet QuitHarry1/22/2023Quiet Quit
33George2/1/2023Fired for IncompentenceHarry1/23/2023Fired for Incompentence
34George2/2/2023SmellsHarry1/24/2023Smells
35George2/3/2023RetiredHarry1/25/2023Quiet Quit
36George2/4/2023Better JobHarry1/26/2023Better Job
37George2/5/2023Own BusinessJohn2/14/2023Retired
38George2/6/2023Quiet QuitJohn2/15/2023Better Job
39Fred2/7/2023Fired for Incompentence
40Fred2/8/2023Smells
41Fred2/9/2023Retired
42Fred2/10/2023Better Job
43Fred2/11/2023Own Business
44Fred2/12/2023Quiet Quit
45Fred2/13/2023Smells
46John2/14/2023Retired
47John2/15/2023Better Job
48John2/16/2023Quiet Quit
49John2/17/2023Fired for Incompentence
50John2/18/2023Smells
51John2/19/2023Retired
52Harry2/20/2023Better Job
53John2/21/2023Own Business
54Harry2/22/2023Quiet Quit
55John2/23/2023Fired for Incompentence
56George2/24/2023Smells
57George2/25/2023Retired
58George2/26/2023Better Job
59Fred2/27/2023Better Job
60Fred2/28/2023Own Business
61Fred3/1/2023Quiet Quit
62Fred3/2/2023Fired for Incompentence
63Fred3/3/2023Smells
64John3/4/2023Retired
Sheet15
Cell Formulas
RangeFormula
F2:H38F2=LET(a,FILTER(A2:C64,(B2:B64>=E2)*(B2:B64<=E3)),b,IF(E1="Name",1,IF(E1="Exit Date",2,3)),SORT(a,b))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E1List=$F$1:$H$1
 
Upvote 0
Another option that is handy if you have more columns to select from.

FILTER and SORT.xlsx
ABCDEFGH
1NameExit DateReasonNameNameExit DateReason
2John1/1/23Fired for Incompentence1/10/23Fred1/10/23Quiet Quit
3John1/2/23Smells2/15/23Fred1/11/23Fired for Incompentence
4John1/3/23RetiredFred1/12/23Smells
5John1/4/23Better JobFred1/13/23Better Job
6John1/5/23Own BusinessFred1/14/23Better Job
7John1/6/23SmellsFred2/7/23Fired for Incompentence
8Fred1/7/23RetiredFred2/8/23Smells
9Fred1/8/23Better JobFred2/9/23Retired
10Fred1/9/23Own BusinessFred2/10/23Better Job
11Fred1/10/23Quiet QuitFred2/11/23Own Business
12Fred1/11/23Fired for IncompentenceFred2/12/23Quiet Quit
13Fred1/12/23SmellsFred2/13/23Smells
14Fred1/13/23Better JobGeorge1/27/23Better Job
15Fred1/14/23Better JobGeorge1/28/23Retired
16Harry1/15/23Own BusinessGeorge1/29/23Better Job
17Harry1/16/23Quiet QuitGeorge1/30/23Own Business
18Harry1/17/23Fired for IncompentenceGeorge1/31/23Quiet Quit
19Harry1/18/23SmellsGeorge2/1/23Fired for Incompentence
20Harry1/19/23Complete JerkGeorge2/2/23Smells
21Harry1/20/23Better JobGeorge2/3/23Retired
22Harry1/21/23Own BusinessGeorge2/4/23Better Job
23Harry1/22/23Quiet QuitGeorge2/5/23Own Business
24Harry1/23/23Fired for IncompentenceGeorge2/6/23Quiet Quit
25Harry1/24/23SmellsHarry1/15/23Own Business
26Harry1/25/23Quiet QuitHarry1/16/23Quiet Quit
27Harry1/26/23Better JobHarry1/17/23Fired for Incompentence
28George1/27/23Better JobHarry1/18/23Smells
29George1/28/23RetiredHarry1/19/23Complete Jerk
30George1/29/23Better JobHarry1/20/23Better Job
31George1/30/23Own BusinessHarry1/21/23Own Business
32George1/31/23Quiet QuitHarry1/22/23Quiet Quit
33George2/1/23Fired for IncompentenceHarry1/23/23Fired for Incompentence
34George2/2/23SmellsHarry1/24/23Smells
35George2/3/23RetiredHarry1/25/23Quiet Quit
36George2/4/23Better JobHarry1/26/23Better Job
37George2/5/23Own BusinessJohn2/14/23Retired
38George2/6/23Quiet QuitJohn2/15/23Better Job
39Fred2/7/23Fired for Incompentence
40Fred2/8/23Smells
41Fred2/9/23Retired
42Fred2/10/23Better Job
43Fred2/11/23Own Business
44Fred2/12/23Quiet Quit
45Fred2/13/23Smells
46John2/14/23Retired
47John2/15/23Better Job
48John2/16/23Quiet Quit
49John2/17/23Fired for Incompentence
50John2/18/23Smells
51John2/19/23Retired
52Harry2/20/23Better Job
53John2/21/23Own Business
54Harry2/22/23Quiet Quit
55John2/23/23Fired for Incompentence
56George2/24/23Smells
57George2/25/23Retired
58George2/26/23Better Job
59Fred2/27/23Better Job
60Fred2/28/23Own Business
61Fred3/1/23Quiet Quit
62Fred3/2/23Fired for Incompentence
63Fred3/3/23Smells
64John3/4/23Retired
Sheet1
Cell Formulas
RangeFormula
F2:H38F2=LET(vList,FILTER(A2:C64,(B2:B64>=E2)*(B2:B64<=E3),""),SORT(vList,XMATCH(E1,A1:C1)))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E1List=$A$1:$C$1
 
Upvote 0
So this is my current filter formula: =IF(FILTER(Table2,Table2[TASK]="Survey","")=0,"",FILTER(Table2,Table2[TASK]="Survey",""))

How do I modify it to sort based on dropdown? I've created dropdown list with several options to select from.

In your formulas, I don't know what a or vList would equate to in mine.
 
Upvote 0
I’m away from my computer so I can’t test it. Your validation list has to match the headers names. Try:

=IF(FILTER(Table2,Table2[TASK]="Survey","")=0,"",
SORT(FILTER(Table2,Table2[TASK]="Survey","")XMATCH(YourValidationCell,YourHeaderRange))

If I’ve made a silly mistake and that doesn’t work, please let us know and we’ll take another stab at it.
 
Upvote 0
Solution
@KevCarter It worked, thank you very much!
Another issue I'm having is I've added data to the array table that isn't on the master list. Everything from SURVEY DATE is only present on the sheet, so when I sort it remains in place while the array table changes. How do I "connect" the two without adding it to the master list? The info on the master list is sorted to different sheets with different data so it wouldn't make sense to put this "extra" info on there. Thoughts?

SORTENGINEER
RMCOMPANYCITYTYPEAPPROVALAPPROVAL DATEQSTRMAUDIT DATEENGINEERREGIONTASKSURVEY DATEDRAWING DATEASSIGNED TOSTATUSNOTES
254VestibuleOutthereVCR10928615AE3355Jan 7, 1900Jan 2, 190043060AbeSouthSurveyMay need a survey to determine adequacy.
226BeachesPeachesAirplanes11530902BC1920Jan 12, 1900Jan 2, 190043119JimSouthSurveySurvey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
498CashGlasgowTapes10727854AD3277Jan 17, 1900Jan 3, 190041940JohnNorthSurveySurvey this site second. Dustin recommends 2 people attend all site visits.
404DiariesOuthouseDogs11731664BD9637Jan 7, 1900Jan 2, 190042664JudasSouthSurveyDrawing started, waiting for feedback from Bryce about location of HP and berms.
344LeyserDenmarkCDs10527091AC1348Jan 6, 1900Jan 3, 190042933LukeNorthSurveyEngineer to discuss with Client.
435FenderHerefordMovies10326331AB2346Jan 9, 1900Jan 3, 190042956MarkNorthSurveyMay need a survey to determine adequacy.
395FeedingPlainsMusic10125569AA1224Jan 8, 1900Jan 3, 190043403MatNorthSurveySurvey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
588BarsMeadowsCows11330139BB6##Jan 17, 1900Jan 2, 190043336MoSouthSurveySurvey this site second. Dustin recommends 2 people attend all site visits.
344PeaveyDalmatianCars11129378BA2989Jan 6, 1900Jan 2, 190043397ZackSouthSurveyDrawing started, waiting for feedback from Bryce about location of HP and berms.
 
Upvote 0
I’m really happy the sorting worked!

I’m going to have to give the next question some thought. I’m terrible about adding to the main table which really destroys the idea of relations. I would do an XLookup to put the data into the main table and hide the columns I don’t need, but I know that’s poor programming…

Hopefully someone else will jump in and we’ll both learn.

Cheers!
 
Upvote 0
Here is me looking up data from another table and adding it to the filter, and keeping the sort working (and stealing Kevin's better way of identifying the sort parameter:
MrExcelPlayground16.xlsx
ABCDEFGHIJKL
1NameExit DateReasonExit DateNameExit DateReasonHeightNameHeight
2John1/1/2023Fired for Incompentence1/10/2023Fred1/10/2023Quiet Quit65John73
3John1/2/2023Smells2/15/2023Fred1/11/2023Fired for Incompentence65Fred65
4John1/3/2023RetiredFred1/12/2023Smells65Harry69
5John1/4/2023Better JobFred1/13/2023Better Job65George71
6John1/5/2023Own BusinessFred1/14/2023Better Job65
7John1/6/2023SmellsHarry1/15/2023Own Business69
8Fred1/7/2023RetiredHarry1/16/2023Quiet Quit69
9Fred1/8/2023Better JobHarry1/17/2023Fired for Incompentence69
10Fred1/9/2023Own BusinessHarry1/18/2023Smells69
11Fred1/10/2023Quiet QuitHarry1/19/2023Complete Jerk69
12Fred1/11/2023Fired for IncompentenceHarry1/20/2023Better Job69
13Fred1/12/2023SmellsHarry1/21/2023Own Business69
14Fred1/13/2023Better JobHarry1/22/2023Quiet Quit69
15Fred1/14/2023Better JobHarry1/23/2023Fired for Incompentence69
16Harry1/15/2023Own BusinessHarry1/24/2023Smells69
17Harry1/16/2023Quiet QuitHarry1/25/2023Quiet Quit69
18Harry1/17/2023Fired for IncompentenceHarry1/26/2023Better Job69
19Harry1/18/2023SmellsGeorge1/27/2023Better Job71
20Harry1/19/2023Complete JerkGeorge1/28/2023Retired71
21Harry1/20/2023Better JobGeorge1/29/2023Better Job71
22Harry1/21/2023Own BusinessGeorge1/30/2023Own Business71
23Harry1/22/2023Quiet QuitGeorge1/31/2023Quiet Quit71
24Harry1/23/2023Fired for IncompentenceGeorge2/1/2023Fired for Incompentence71
25Harry1/24/2023SmellsGeorge2/2/2023Smells71
26Harry1/25/2023Quiet QuitGeorge2/3/2023Retired71
27Harry1/26/2023Better JobGeorge2/4/2023Better Job71
28George1/27/2023Better JobGeorge2/5/2023Own Business71
29George1/28/2023RetiredGeorge2/6/2023Quiet Quit71
30George1/29/2023Better JobFred2/7/2023Fired for Incompentence65
31George1/30/2023Own BusinessFred2/8/2023Smells65
32George1/31/2023Quiet QuitFred2/9/2023Retired65
33George2/1/2023Fired for IncompentenceFred2/10/2023Better Job65
34George2/2/2023SmellsFred2/11/2023Own Business65
35George2/3/2023RetiredFred2/12/2023Quiet Quit65
36George2/4/2023Better JobFred2/13/2023Smells65
37George2/5/2023Own BusinessJohn2/14/2023Retired73
38George2/6/2023Quiet QuitJohn2/15/2023Better Job73
39Fred2/7/2023Fired for Incompentence
40Fred2/8/2023Smells
41Fred2/9/2023Retired
42Fred2/10/2023Better Job
43Fred2/11/2023Own Business
44Fred2/12/2023Quiet Quit
45Fred2/13/2023Smells
46John2/14/2023Retired
47John2/15/2023Better Job
48John2/16/2023Quiet Quit
49John2/17/2023Fired for Incompentence
50John2/18/2023Smells
51John2/19/2023Retired
52Harry2/20/2023Better Job
53John2/21/2023Own Business
54Harry2/22/2023Quiet Quit
55John2/23/2023Fired for Incompentence
56George2/24/2023Smells
57George2/25/2023Retired
58George2/26/2023Better Job
59Fred2/27/2023Better Job
60Fred2/28/2023Own Business
61Fred3/1/2023Quiet Quit
62Fred3/2/2023Fired for Incompentence
63Fred3/3/2023Smells
64John3/4/2023Retired
Sheet15
Cell Formulas
RangeFormula
F2:I38F2=LET(a,FILTER(A2:C64,(B2:B64>=E2)*(B2:B64<=E3)),b,XMATCH(E1,F1:I1),c,XLOOKUP(TAKE(a,,1),K2:K5,L2:L5,,0),d,HSTACK(a,c),SORT(d,b))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E1List=$F$1:$I$1
 
Upvote 0
You must log in or register to reply here.

Similar threads

FrumpyJones
  • Question
Is it possible to do a multiple Filter-sort?
Replies
3
Views
154
Kaper
Kaper
H
  • Question
Filter, sorted, long hstack() formula
Replies
2
Views
86
henryg
H
G
  • Question
How to use the Filter function to create a single list from 3 different arrays
2
Replies
10
Views
203
KRice
K
JenniferMurphy
  • Question
Is there a way to create a table sort button that is separate from the table and doesn't use a macro?
Replies
2
Views
162
JenniferMurphy
JenniferMurphy
B
  • Question
Using one formula to spill customer across 7 columns
Replies
0
Views
28
bearcub
B

Forum statistics

Threads
1,215,219
Messages
6,123,689
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top