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

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Before sortAfter sort
2DayDrug_LevelDayDrug_Level
328620223screening6191016
467951988baseline1238085
53812087228620223
69120902838120872
7baseline55490705932997
87126222866792422
914106130071262228
10screening123808592159194
11303373614925183.5
12223576752223576752
13931093602571212
1459329973033736
152571212
1614789067
17baseline6832962
1865632856
19
Sheet1


Thanks for any input.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
How about
Fluff.xlsm
ABCDE
1Before sort
2DayDrug_Level
328620223screening1238085
467951988baseline6191016
53812087228620223
69120902838120872
7baseline55490705932997
87126222866792422
914106130071262228
10screening123808592159194
11303373614925183.5
12223576752223576752
13931093602571212
1459329973033736
152571212
1614789067
17baseline6832962
1865632856
19
Data
Cell Formulas
RangeFormula
D3:D14D3=UNIQUE(SORTBY(SORT(A3:A18),MATCH(SORT(A3:A18),{"screening","baseline"},0),1))
E3:E14E3=AVERAGEIFS(B:B,A:A,D3#)
Dynamic array formulas.
 
Upvote 0
Solution
Glad to help & thanks for the feedback.
 
Upvote 0
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:

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
ABCDEFG
1Before sort
2IDDayDrug_Level
3A128620223screening1238085
4A167951988baseline6191016
5A13812087228620223
6A19120902838120872
7A1baseline55490705932997
8A17126222866792422
9A114106130071262228
10A1screening123808592159194
11A1303373614925183.5
12A1223576752223576752
13A1931093602571212
14A159329973033736
15A12571212
16A114789067
17A1baseline6832962
18A165632856
19
Sheet1
Cell Formulas
RangeFormula
E3:E14E3=UNIQUE(SORTBY(SORT(FILTER(B3:B18,A3:A18="A1")),MATCH(SORT(FILTER(B3:B18,A3:A18="A1")),{"screening","baseline"},0),1))
F3:F14F3=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! 🤗
 
Upvote 0
Why not just use the two columns with the formulae you already have?
 
Upvote 0
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.)
 
Upvote 0
So this would be more like the larger data set I have:

Book1
ABCDEFG
1Before sort
2IDDayDrug_Level
3A128620223screening1238085
4A167951988baseline6191016
5A13812087228620223
6A19120902838120872
7A1baseline55490705634872.5
8A17126222864687246
9A114106130071262228
10A1screening123808592159194
11A1303373614925183.5
12A1223576752223576752
13A1931093602571212
14A159329973033736
15A12571212
16A114789067
17A1baseline6832962
18A165632856
19A26476894
20A213782187
21A25336748
22etc.
23
Sheet1
Cell Formulas
RangeFormula
E3:E14E3=UNIQUE(SORTBY(SORT(FILTER(B3:B21,A3:A21="A1")),MATCH(SORT(FILTER(B3:B18,A3:A18="A1")),{"screening","baseline"},0),1))
F3:F14F3=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.
 
Upvote 0
Just change the average to
Excel Formula:
=AVERAGEIFS(C:C,B:B,E3#,A:A,"A1")
 
Upvote 0
Yes, that worked pretty well, thank you! 🤗

I was overthinking it 😅
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

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