Waiting list

Hi to all experts of this forum,

I want to create a waiting list for patients at a rural health care NGO in India. All patients are given Numbers (column B,D and F) and are allocated to specific doctors A,B and/or C in Row 1.
Some patients are allocated to several doctors but if that is the case, they can not be mentioned in two waiting lists at the same time.

The waiting list is B14:G24. Here the patients are supposed to be listed if they're not already served, which would be noted as a 1 in column C, E or G.

So the task, that I am unable so solve is to list all the patients that have a 0 next to their number in the concerning waiting list.
After a patient is served and he gets a 1 instead of a 0 next to his number, he is supposed to dissapear from the waiting list and the next patient with a 0 should appear at the very bottom of the list. The so far second in the list should move up to the place of the first one.

I would be so happy if someone could give me some help to fill the cells of the waiting list. I tried it with the index function for a long time, but it didnt work.

Kind Regards
Praveen

Hi Praveen

You should be using Macros for this.

My First Impression is that this could be expanded to make this really useful.

Ok Part One.

This Deletes a Patient from the queue when you change their status to 1.

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

This extended Version Allows you to add new patients to any queue.
NB: It deletes any previous entry for that patient.

Please Login or Register  to view this content.

Ok

Try this version.

Select Cell A5

Enter 15ABC

Then Select B5 and enter 1.


I think that will work for you.

Ok do this.

Enter 14ABC into Cell A5 ............... You are saying Patient 14 is in Waiting List A, but he also needs to see doctor B and C.

Enter 1 into Cell B5 ............... You are saying that Patient 14 has seen Doctor A.
14ABC will disappear From Cell A5
However 14BC will Appear in Column D.
This means that Patient 14 is in Waiting List B, but he also needs to see doctor C.

Enter 1 next to 14BC ............... You are saying that Patient 14 has seen Doctor B.
14ABC will disappear and you will see 14C in Column F
This means that Patient 14 is in Waiting List C, Waiting to see doctor C.



I have improved the sorter subroutine.

Please Login or Register  to view this content.

Please try at B13 and copy over

=IFERROR(INDEX(B$1:B$10,AGGREGATE(15,6,ROW($B$3:$B$10)/NOT(C$3:C$10),ROWS(B$14:B14))),"")

I think I attached a previous version. Try the attached a sample file.