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

1 Answer 1


I modified the helper column on L5 =ArrayFormula(text(ArrayFormula(IFs(REGEXMATCH(C5:C, "-"), SUBSTITUTE(C5:C,"-",""), C5:C<>"-",C5:C*10)),"00000")) this will make all room/bed numbers in 00000 format. Then they will be sorted in the tab of (All_Patients) by this formula =query(Data_Entry!B5:L, "select B,C,D,E,H,F,G,J,I,L where B<>'Ward' AND F is not null order by I,B,L",0). It seems I have put two ArrayFomula functions in the same cell and it worked. Any idea if I can simplify it in a better way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.