How to make certain results appear at the beginning of SORT, followed by all other results?

[Rnkhch]

Hello,

I have some data for "drug_level", that I need to sort based on the day column, but I want the first two results to always correspond to the drug_level on the "screening" and "baseline" days, and then all other days sorted in increasing order. Also if there are more than one values for a certain day, I want them to be averaged. Here is an example XL2BB:

Book1
	A	B	C	D	E	F	G
1	Before sort				After sort
2	Day	Drug_Level			Day	Drug_Level
3	2	8620223			screening	6191016
4	6	7951988			baseline	1238085
5	3	8120872			2	8620223
6	9	1209028			3	8120872
7	baseline	5549070			5	932997
8	7	1262228			6	6792422
9	14	1061300			7	1262228
10	screening	1238085			9	2159194
11	30	33736			14	925183.5
12	22	3576752			22	3576752
13	9	3109360			25	71212
14	5	932997			30	33736
15	25	71212
16	14	789067
17	baseline	6832962
18	6	5632856
19
Sheet1

Thanks for any input.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Before sort
2	Day	Drug_Level
3	2	8620223		screening	1238085
4	6	7951988		baseline	6191016
5	3	8120872		2	8620223
6	9	1209028		3	8120872
7	baseline	5549070		5	932997
8	7	1262228		6	6792422
9	14	1061300		7	1262228
10	screening	1238085		9	2159194
11	30	33736		14	925183.5
12	22	3576752		22	3576752
13	9	3109360		25	71212
14	5	932997		30	33736
15	25	71212
16	14	789067
17	baseline	6832962
18	6	5632856
19
Data

Cell Formulas
Range	Formula
D3:D14	D3	=UNIQUE(SORTBY(SORT(A3:A18),MATCH(SORT(A3:A18),{"screening","baseline"},0),1))
E3:E14	E3	=AVERAGEIFS(B:B,A:A,D3#)
Dynamic array formulas.

 

[Rnkhch]

Fabulous! Thanks for fast response!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Rnkhch]

Hi Fluff,

Any chance you can make your solution work with arrays (involving filters) as well?

AVERAGEIFS is another one of those functions that is restricted to "ranges" only, and it doesn't work with "arrays" and "defined names".

This is essentially the same situation in my other question from a few weeks ago involving COUNTIFS:

How to make COUNTIF(S) work with arrays?

Hello, Unless I'm missing something, it appears to me that both COUNTIF and COUNTIFS only work with ranges, i.e. continuous selections of cells. When I try to put a filtered array for the range parameter, the formulas don't accept it. For instance, in the attached XL2BB, I'm trying to count...

www.mrexcel.com

In fact, I was trying to apply the solutions there to your solution here and see if I could make it work, but so far I've been stuck.

Here is the updated XL2BB where I have replaced your ranges with arrays:

Book1
	A	B	C	D	E	F	G
1		Before sort
2	ID	Day	Drug_Level
3	A1	2	8620223		screening	1238085
4	A1	6	7951988		baseline	6191016
5	A1	3	8120872		2	8620223
6	A1	9	1209028		3	8120872
7	A1	baseline	5549070		5	932997
8	A1	7	1262228		6	6792422
9	A1	14	1061300		7	1262228
10	A1	screening	1238085		9	2159194
11	A1	30	33736		14	925183.5
12	A1	22	3576752		22	3576752
13	A1	9	3109360		25	71212
14	A1	5	932997		30	33736
15	A1	25	71212
16	A1	14	789067
17	A1	baseline	6832962
18	A1	6	5632856
19
Sheet1

Cell Formulas
Range	Formula
E3:E14	E3	=UNIQUE(SORTBY(SORT(FILTER(B3:B18,A3:A18="A1")),MATCH(SORT(FILTER(B3:B18,A3:A18="A1")),{"screening","baseline"},0),1))
F3:F14	F3	=AVERAGEIFS(C:C,B:B,E3#)
Dynamic array formulas.

I was trying to write this function for E3 when I realized arrays are not gonna work with AVERAGEIFS:

E3=AVERAGEIFS(SORT(FILTER(C3:C18,A3:A18="A1")),SORT(FILTER(B3:B18,A3:A18="A1")),SORT(FILTER(E3:E18,A3:A18="A1")))

Thank you!

 

[Fluff]

Why not just use the two columns with the formulae you already have?

 

[Rnkhch]

Oh, in a larger data set, I would need to filter the Day and Drug columns by the subject ID (column A). And so when I apply the filter to ranges, then the AVERAGEIFS doesn't work. (For brevity, I left out additional subject IDs from the example XL2BB.)

 

[Rnkhch]

So this would be more like the larger data set I have:

Book1
	A	B	C	D	E	F	G
1		Before sort
2	ID	Day	Drug_Level
3	A1	2	8620223		screening	1238085
4	A1	6	7951988		baseline	6191016
5	A1	3	8120872		2	8620223
6	A1	9	1209028		3	8120872
7	A1	baseline	5549070		5	634872.5
8	A1	7	1262228		6	4687246
9	A1	14	1061300		7	1262228
10	A1	screening	1238085		9	2159194
11	A1	30	33736		14	925183.5
12	A1	22	3576752		22	3576752
13	A1	9	3109360		25	71212
14	A1	5	932997		30	33736
15	A1	25	71212
16	A1	14	789067
17	A1	baseline	6832962
18	A1	6	5632856
19	A2	6	476894
20	A2	13	782187
21	A2	5	336748
22	etc.
23
Sheet1

Cell Formulas
Range	Formula
E3:E14	E3	=UNIQUE(SORTBY(SORT(FILTER(B3:B21,A3:A21="A1")),MATCH(SORT(FILTER(B3:B18,A3:A18="A1")),{"screening","baseline"},0),1))
F3:F14	F3	=AVERAGEIFS(C:C,B:B,E3#)
Dynamic array formulas.

If I don't filter the data by subject ID, then your solution will take all data as for one patient.

 

[Fluff]

Just change the average to

=AVERAGEIFS(C:C,B:B,E3#,A:A,"A1")

 

[Rnkhch]

Yes, that worked pretty well, thank you!

I was overthinking it