Custom-sort filter array table

[freddyt]

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?

 

[freddyt]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Exit Date	Reason		Exit Date	Name	Exit Date	Reason	Height		Name	Height
2	John	1/1/2023	Fired for Incompentence		1/10/2023	Fred	1/10/2023	Quiet Quit	65		John	73
3	John	1/2/2023	Smells		2/15/2023	Fred	1/11/2023	Fired for Incompentence	65		Fred	65
4	John	1/3/2023	Retired			Fred	1/12/2023	Smells	65		Harry	69
5	John	1/4/2023	Better Job			Fred	1/13/2023	Better Job	65		George	71
6	John	1/5/2023	Own Business			Fred	1/14/2023	Better Job	65
7	John	1/6/2023	Smells			Harry	1/15/2023	Own Business	69
8	Fred	1/7/2023	Retired			Harry	1/16/2023	Quiet Quit	69
9	Fred	1/8/2023	Better Job			Harry	1/17/2023	Fired for Incompentence	69
10	Fred	1/9/2023	Own Business			Harry	1/18/2023	Smells	69
11	Fred	1/10/2023	Quiet Quit			Harry	1/19/2023	Complete Jerk	69
12	Fred	1/11/2023	Fired for Incompentence			Harry	1/20/2023	Better Job	69
13	Fred	1/12/2023	Smells			Harry	1/21/2023	Own Business	69
14	Fred	1/13/2023	Better Job			Harry	1/22/2023	Quiet Quit	69
15	Fred	1/14/2023	Better Job			Harry	1/23/2023	Fired for Incompentence	69
16	Harry	1/15/2023	Own Business			Harry	1/24/2023	Smells	69
17	Harry	1/16/2023	Quiet Quit			Harry	1/25/2023	Quiet Quit	69
18	Harry	1/17/2023	Fired for Incompentence			Harry	1/26/2023	Better Job	69
19	Harry	1/18/2023	Smells			George	1/27/2023	Better Job	71
20	Harry	1/19/2023	Complete Jerk			George	1/28/2023	Retired	71
21	Harry	1/20/2023	Better Job			George	1/29/2023	Better Job	71
22	Harry	1/21/2023	Own Business			George	1/30/2023	Own Business	71
23	Harry	1/22/2023	Quiet Quit			George	1/31/2023	Quiet Quit	71
24	Harry	1/23/2023	Fired for Incompentence			George	2/1/2023	Fired for Incompentence	71
25	Harry	1/24/2023	Smells			George	2/2/2023	Smells	71
26	Harry	1/25/2023	Quiet Quit			George	2/3/2023	Retired	71
27	Harry	1/26/2023	Better Job			George	2/4/2023	Better Job	71
28	George	1/27/2023	Better Job			George	2/5/2023	Own Business	71
29	George	1/28/2023	Retired			George	2/6/2023	Quiet Quit	71
30	George	1/29/2023	Better Job			Fred	2/7/2023	Fired for Incompentence	65
31	George	1/30/2023	Own Business			Fred	2/8/2023	Smells	65
32	George	1/31/2023	Quiet Quit			Fred	2/9/2023	Retired	65
33	George	2/1/2023	Fired for Incompentence			Fred	2/10/2023	Better Job	65
34	George	2/2/2023	Smells			Fred	2/11/2023	Own Business	65
35	George	2/3/2023	Retired			Fred	2/12/2023	Quiet Quit	65
36	George	2/4/2023	Better Job			Fred	2/13/2023	Smells	65
37	George	2/5/2023	Own Business			John	2/14/2023	Retired	73
38	George	2/6/2023	Quiet Quit			John	2/15/2023	Better Job	73
39	Fred	2/7/2023	Fired for Incompentence
40	Fred	2/8/2023	Smells
41	Fred	2/9/2023	Retired
42	Fred	2/10/2023	Better Job
43	Fred	2/11/2023	Own Business
44	Fred	2/12/2023	Quiet Quit
45	Fred	2/13/2023	Smells
46	John	2/14/2023	Retired
47	John	2/15/2023	Better Job
48	John	2/16/2023	Quiet Quit
49	John	2/17/2023	Fired for Incompentence
50	John	2/18/2023	Smells
51	John	2/19/2023	Retired
52	Harry	2/20/2023	Better Job
53	John	2/21/2023	Own Business
54	Harry	2/22/2023	Quiet Quit
55	John	2/23/2023	Fired for Incompentence
56	George	2/24/2023	Smells
57	George	2/25/2023	Retired
58	George	2/26/2023	Better Job
59	Fred	2/27/2023	Better Job
60	Fred	2/28/2023	Own Business
61	Fred	3/1/2023	Quiet Quit
62	Fred	3/2/2023	Fired for Incompentence
63	Fred	3/3/2023	Smells
64	John	3/4/2023	Retired
Sheet15

Cell Formulas
Range	Formula
F2:I38	F2	=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
Cell	Allow	Criteria
E1	List	=$F$1:$I$1

@JamesCanale what do a,b, c, and d represent in your data?

 

[JamesCanale]

I'm bad at naming variables. It goes back to my commodore 64 days...

a is simply the filtered data table. b is the number of the column of that matches the drop-down - for use in the SORT at the end. c is the column of looked up heights - you can see it looks up from the first TAKEn column of a (the names) and looks in the secondary data table of peoples heights. d is a merged table of the first filtered set (a) and the appended heights (c). Then the sort of the merged data table (d) by the column that matches the drop down.

 

[freddyt]

I'm bad at naming variables. It goes back to my commodore 64 days...

a is simply the filtered data table. b is the number of the column of that matches the drop-down - for use in the SORT at the end. c is the column of looked up heights - you can see it looks up from the first TAKEn column of a (the names) and looks in the secondary data table of peoples heights. d is a merged table of the first filtered set (a) and the appended heights (c). Then the sort of the merged data table (d) by the column that matches the drop down.

I'm bad at naming variables. It goes back to my commodore 64 days...

a is simply the filtered data table. b is the number of the column of that matches the drop-down - for use in the SORT at the end. c is the column of looked up heights - you can see it looks up from the first TAKEn column of a (the names) and looks in the secondary data table of peoples heights. d is a merged table of the first filtered set (a) and the appended heights (c). Then the sort of the merged data table (d) by the column that matches the drop down.

@JamesCanale Can you please humor me and do the same with my table?

Task Tracker Rebuild v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	SORT	ENGINEER
2
3	RM	COMPANY	CITY	TYPE	APPROVAL	APPROVAL DATE	Q	S	T	R	M	AUDIT DATE	ENGINEER	REGION	TASK	SURVEY DATE	DRAWING DATE	ASSIGNED TO	STATUS	NOTES
4	254	Vestibule	Outthere	VCR	109	28615	AE	33	55	Jan 7, 1900	Jan 2, 1900	43060	Abe	South	Survey					May need a survey to determine adequacy.
5	226	Beaches	Peaches	Airplanes	115	30902	BC	19	20	Jan 12, 1900	Jan 2, 1900	43119	Jim	South	Survey					Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
6	498	Cash	Glasgow	Tapes	107	27854	AD	32	77	Jan 17, 1900	Jan 3, 1900	41940	John	North	Survey					Survey this site second. Dustin recommends 2 people attend all site visits.
7	404	Diaries	Outhouse	Dogs	117	31664	BD	96	37	Jan 7, 1900	Jan 2, 1900	42664	Judas	South	Survey					Drawing started, waiting for feedback from Bryce about location of HP and berms.
8	344	Leyser	Denmark	CDs	105	27091	AC	13	48	Jan 6, 1900	Jan 3, 1900	42933	Luke	North	Survey					Engineer to discuss with Client.
9	435	Fender	Hereford	Movies	103	26331	AB	23	46	Jan 9, 1900	Jan 3, 1900	42956	Mark	North	Survey					May need a survey to determine adequacy.
10	395	Feeding	Plains	Music	101	25569	AA	12	24	Jan 8, 1900	Jan 3, 1900	43403	Mat	North	Survey					Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
11	588	Bars	Meadows	Cows	113	30139	BB	6	##	Jan 17, 1900	Jan 2, 1900	43336	Mo	South	Survey					Survey this site second. Dustin recommends 2 people attend all site visits.
12	344	Peavey	Dalmatian	Cars	111	29378	BA	29	89	Jan 6, 1900	Jan 2, 1900	43397	Zack	South	Survey					Drawing started, waiting for feedback from Bryce about location of HP and berms.
Surveys

Cell Formulas
Range	Formula
A4:O12	A4	=IF(SORT(FILTER(Table2,Table2[TASK]="Survey"),XMATCH(B1,A3:S3))="","",SORT(FILTER(Table2,Table2[TASK]="Survey"),XMATCH(B1,A3:S3)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
L4:L12	List	Bryce, Chantel, Chris, Cory, Dustin, Lee, Peta-Gay, Priscila, Sheldon
N4:N12	List	Survey, Drawing, Complete
B1	List	=$A$3:$S$3
O3	List	Audit, Re-Inspection, Compliance, Application, Approval, Survey, Complaint, Site Inspection

 

[JamesCanale]

post your original data from table2; and the other table you want to connect to it.

 

[freddyt]

post your original data from table2; and the other table you want to connect to it.

This is the original data. It'll be filtered to another tab based on the task.

Task Tracker Rebuild v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	RM	COMPANY	CITY	TYPE	APPROVAL	APPROVAL DATE	Q	S	T	R	M	AUDIT DATE	ENGINEER	REGION	TASK
2	395	Feeding	Plains	Music	101	Jan 1, 1970	AA	12	24	8	3	Oct 30, 2018	Mat	North	Survey
3	435	Fender	Hereford	Movies	103	Feb 2, 1972	AB	23	46	9	3	Aug 9, 2017	Mark	North	Survey
4	344	Leyser	Denmark	CDs	105	Mar 3, 1974	AC	13	48	6	3	Jul 17, 2017	Luke	North	Survey
5	498	Cash	Glasgow	Tapes	107	Apr 4, 1976	AD	32	77	17	3	Oct 28, 2014	John	North	Survey
6	254	Vestibule	Outthere	VCR	109	May 5, 1978	AE	33	55	7	2	Nov 21, 2017	Abe	South	Survey
7	344	Peavey	Dalmatian	Cars	111	Jun 6, 1980	BA	29	89	6	2	Oct 24, 2018	Zack	South	Survey
8	588	Bars	Meadows	Cows	113	Jul 7, 1982	BB	6	106	17	2	Aug 24, 2018	Mo	South	Survey
9	226	Beaches	Peaches	Airplanes	115	Aug 8, 1984	BC	19	20	12	2	Jan 19, 2018	Jim	South	Survey
10	404	Diaries	Outhouse	Dogs	117	Sep 9, 1986	BD	96	37	7	2	Oct 21, 2016	Judas	South	Survey
11
Master List

Cells with Data Validation
Cell	Allow	Criteria
N2:N11	List	North, South
O2:O11	List	Audit, Re-Inspection, Compliance, Application, Approval, Survey, Complaint

This is the data on the tab where the original data is filtered to.

Task Tracker Rebuild v1.xlsx
	P	Q	R	S	T
3	SURVEY DATE	DRAWING DATE	ASSIGNED TO	STATUS	NOTES
4	Nov 21, 2017	Nov 21, 2017	Abe	Complete	May need a survey to determine adequacy.
5	Jan 19, 2018	Jan 19, 2018	Jim	In Progress	Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
6	Oct 28, 2014	Oct 28, 2014	John	Oh Hold	Survey this site second. Dustin recommends 2 people attend all site visits.
7	Oct 21, 2016	Oct 21, 2016	Judas	Complete	Drawing started, waiting for feedback from Bryce about location of HP and berms.
8	Jul 17, 2017	Jul 17, 2017	Luke	In Progress	Engineer to discuss with Client.
9	Aug 9, 2017	Aug 9, 2017	Mark	Oh Hold	May need a survey to determine adequacy.
10	Oct 30, 2018	Oct 30, 2018	Mat	Complete	Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
11	Aug 24, 2018	Aug 24, 2018	Mo	In Progress	Survey this site second. Dustin recommends 2 people attend all site visits.
12	Oct 24, 2018	Oct 24, 2018	Zack	Oh Hold	Drawing started, waiting for feedback from Bryce about location of HP and berms.
Surveys

 

[JamesCanale]

How is this supposed to be connected? This information on Survey Date, Drawing Date...Notes connected to the RM number (Column A).

I my simple example, I had Name, Date, Reason in the main data table - and filtered it by a date range. In the second data table I had name and height as a smaller lookup table. So I was able to connect the tables because name was in both of them.

I gather that someone had the filter going and then just put more data next to it. That won't work. If there was a secondary table that was "Survey Info", and linked by some key like "RM" - then they could all be pulled together and sorted nicely.

The short of it is: you can't just put new notes next to a filtered set and expect excel to be able to deal with it for sorting purposes.

So if that bottom table of survey information was somewhere else, and had a column with "RM" numbers to link up, you would be in business.

 

[freddyt]

How is this supposed to be connected? This information on Survey Date, Drawing Date...Notes connected to the RM number (Column A).

I my simple example, I had Name, Date, Reason in the main data table - and filtered it by a date range. In the second data table I had name and height as a smaller lookup table. So I was able to connect the tables because name was in both of them.

I gather that someone had the filter going and then just put more data next to it. That won't work. If there was a secondary table that was "Survey Info", and linked by some key like "RM" - then they could all be pulled together and sorted nicely.

The short of it is: you can't just put new notes next to a filtered set and expect excel to be able to deal with it for sorting purposes.

So if that bottom table of survey information was somewhere else, and had a column with "RM" numbers to link up, you would be in business.

Riiight! And herein lies my problem. Thing is I have six different tasks with their own sheets, so I'd have to create another tab for each one to link to the filtered set. In that case I might as well not have a master list and just add text without formulas, no?

 

[JamesCanale]

I might put it all together and then use the native sort and filtering to filter the data and then sort in one big filtered table.

 

[KevCarter]

WOW James, beautiful formula. You make great use of the new functions and I'm learning from your work. Thank you!

 

[freddyt]

Hi Gentlemen! After much daydreaming I figured out a workaround this issue. The only thing I don't like about it is the blank cells returned if criteria aren't met. How do I get the results stacked one row after another instead of the blanks?

Task Tracker Rebuild v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3	RM	COMPANY	CITY	TYPE	APPROVAL	APPROVAL DATE	Q	S	T	R	M	AUDIT DATE	ENGINEER	REGION	TASK	SURVEY DATE	DRAWING DATE	ASSIGNED TO	STATUS	NOTES
4	395	Feeding	Plains	Music	101	25569	AA	12	24	8	3	43403	Mat	North	Survey	43060	43060	Abe	Complete	May need a survey to determine adequacy.
5																42933	42933	Luke	In Progress	Engineer to discuss with Client.
6	344	Leyser	Denmark	CDs	105	27091	AC	13	48	6	3	42933	Luke	North	Survey	43336	43336	Mo	In Progress	Survey this site second. Dustin recommends 2 people attend all site visits.
7	498	Cash	Glasgow	Tapes	107	27854	AD	32	77	17	3	41940	John	North	Survey	42956	42956	Mark	Oh Hold	May need a survey to determine adequacy.
8																43397	43397	Zack	Oh Hold	Drawing started, waiting for feedback from Bryce about location of HP and berms.
9	344	Peavey	Dalmatian	Cars	111	29378	BA	29	89	6	2	43397	Zack	South	Survey	43119	43119	Jim	In Progress	Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
10	588	Bars	Meadows	Cows	113	30139	BB	6	106	17	2	43336	Mo	South	Survey	43403	43403	Mat	Complete	Survey this site first before starting the other site. Dustin recommends 2 people attend all site visits.
11																42664	42664	Judas	Complete	Drawing started, waiting for feedback from Bryce about location of HP and berms.
12	404	Diaries	Outhouse	Dogs	117	31664	BD	96	37	7	2	42664	Judas	South	Survey	41940	41940	John	Oh Hold	Survey this site second. Dustin recommends 2 people attend all site visits.
Sheet1

Cell Formulas
Range	Formula
A4:O12	A4	=IF('Master List'!$O2="Survey",'Master List'!A2,"")