I have this sheet that I enter all data of patients in (Data_Entry) tab and I want these data to be sorted by Room number and bed number in the form of 00-00. For example Room 4 bed2 (4-2) and so on. I added a helper column L that modify the room/bed number in a way I remove the "-" and I multiply by 10 if the number entered has no "-" because some rooms are only one bedded room. Then I query all the data in All_patients Tab to be sorted first by unit(Column I), then by ward(Column B), then by modified Room&Bed (helper Column L). Because sorting by Column C (actual numbers like 4-3) doesn't result in correct ascending numbers.
But I had a problem at the results in All_Patients tab that the original helper column L(Data_Entry) which is Column K in the results tab(All_Patients) doesn't show all the values of the numbers.So, that resulted in an incorrect ascending order by room& bed number.
=ArrayFormula(IFs(REGEXMATCH(C5:C, "-"), SUBSTITUTE(C5:C,"-",""), C5:C<>"-",C5:C*10)) this formula is in L5 of (Data_ENtry)tab that modifies the room/bed numbers in actual numbers that can be sorted if needed.
=query(Data_Entry!B5:L,"select B,C,D,E,H,F,G,K,I,L where F is not null AND B<>'Ward' order by I,B,L",0) this formula is in B5 of (All_Patients) tab that brings all entry data and sort them by unit,ward,room.
Probably there is a smarter way of sorting room/bed number
