Help with Multiple Entry Detection in a table

[Volnix]

Hi all!

I am new here and hoped to get some help in this thing that I am doing for a medical appointment entry excel table.

The table has entries, which are unique by a code. Then I would like to check how many "similar" codes are trying to get an appointment on the same day and alert the user that there is another entry, by a "similar" code (more on those similar codes later) in that date.

The codes represent patients which are in a "family" lets say of codes. An example code would be X444f for one and X444e for another code, both in the X444 family. The later ones have a double letter prefix eg. XX444F

The last character of those codes is what truly represents the "uniqueness" as to say of the code.

then the user is doing an entry, by entering also a Date of Appointment.


The formula I am using now is the COUNTIFS so I can have the number of multiple entries by that "Code Family" in that specific date. What I want it to do is to display how many appointments from the code family are being made for the same date and avoid that.


This is the function I am using now: Problem is that allthough it "seems" to work for multiple entries, by displaying the number 2 for example, when there is a single entry it displays "1" which is not a double entry.

Can you think of a way to work this? For example how to look for all entries exept self and only display something when the entries for that specific date are actually multiple from the same family of the code?

Here's my fuction at the moment and an example segment of the table I am working on


=countifs(Y:Y;Y2;AJ:AJ;AJ2;Q:Q;"="&Q2)


Column Y is the date

Column AJ is the "Code family" which is calculated by replacing the codes last digits with nothing with this function: =replace(E2;5;100;"") so W555f becomes W555 If you know a way for this to work with double character prefixes please say.. Example XX444f to become XX444

Column Q is a string which describes the appointment premises.

So any way to just alert the user when there are MULTIPLE entries of a family code (not unique codes) on a specific day? If it was to display some string as well, for example "Warning, there are another 2 appointments of the same family for that day. Is should not display just "1" as this is a unique entry.

Thank you very much!

[PaulM100]

Upload a sample file with desired results as well. To upload GO Advanced - Manage attachments -

[Volnix]

Ok I am uploading a downloaded file from google sheets which is kinda what I am trying to do..

The problem with deleting the last character from the family codes is solved.

But now the problem remains with the multiple appointments. It shows "1" it should show 0... Does It count it self?

Note, the code in the downloaded xls went all screwy, so the code i am using is the same as in my first post, this:

=countifs(Y:Y;Y2;AJ:AJ;AJ2;Q:Q;"="&Q2)

and I think I just changed it to:

=countifs(Y:Y;Y2;AJ:AJ;AJ2;Q:Q;Q2)

(in the attached file the column letters are different)

any ideas ??

Thanks!

Untitled.png

[Aardigspook]

Try something like this maybe:

Please Login or Register  to view this content.

[PaulM100]

Maybe one of these as well:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

With some IF tweaks for warnings