Count entries in a list and prevent duplicates

  • Hi,

    I am new to this forum so I am not sure where to post or what to do here. I have a very simple excel sheet which lists doctors names and was wondering if I can simplify my work some how. I have to count the doctors in the list to ensure there are no duplicates and give each of them a value to ensure it always adds up to 23. If a doctor is listed with just his name, he has a value of "1", if he is listed with an "am or a "pm" after his name, he is given a value of "0.5". I then add all the values and make sure it is "23" total and that no doctor is listed twice. is there a way to accomplish this? Ive messed around with drop down boxes, and messed with group boxes, but can't figure out what to do here. can anyone point me anywhere or help me with what to do? I spend so much time on this and is always "human error" prone to my arithmetic. Thanks for any help from anyone. I have attached a sample of what I have to enter.forum.ozgrid.com/index.php?attachment/33208/

  • Re: Looking for some guidance on how to count entries in cells


    Thanks for the quick reply. You have the idea, but it is not quite right yet. For example:

    B3 has two doctors "Iskos" and "Li". Now "Li" has an "am" next to him so for that cell "Iskos" gets a "1" and "Li" gets a "0.5". For cell B3 the total value then is "1.5". I would then goto cell B4 and see what the toal was and the finally add B3:B16 and ensure it is all "23" and that no duplicates exist, unless they have an "am" or pm" in which that is ok. Just no doctor can have more than a value of "1". Does that make sense? I can explain more if needed.

  • Re: Looking for some guidance on how to count entries in cells


    Ok i think i understan. Im working on a form for you. Do the dr's name have to be in the same slot?

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Looking for some guidance on how to count entries in cells


    I have a whole month of the same type of data entry. Each day of the month will always be different with names in different orders. I only cut-n-pasted a days worth of work to show what it looks like as a sample. I could post the entire sheet if youd like to see that. I add doctors for every day of the month for various clinics and stuff, so names will always be different for each day. The thing that throws me off every day is when more than one doctor is in a cell. I cant figure out how to seperate them for a count. I know I could use multiple cells, but the sheet is so large as it is, it is easier to use one cell to list all docotrs at one time.

  • Re: Looking for some guidance on how to count entries in cells


    Yeah post the book. It will be easier to fix it that way.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Looking for some guidance on how to count entries in cells


    sounds good. I need to run my son to school real quick, but will be back. I REALLY appreciate your help. Shoot I had to cut-n-paste a week worth of data. My original file is too big for attachments on this forum. Ill work on that when I get back, but maybe you can get an idea of this attachment iwth a week. The main file is exactly the same with 4 weeks, just various doctors in the fields for the various clinics/procedures.

  • Re: Looking for some guidance on how to count entries in cells


    looking

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Looking for some guidance on how to count entries in cells


    OK I'am back. Just to see if anything was done yet. No hurry, just really appreciate your help.

  • Re: Looking for some guidance on how to count entries in cells


    Ok here is the problem. Im not aware of a formula that will look at multiple sets of values with different criteria. The form needs to be broken down a little. For example : 1 line should contain 2 cells 1 for Doctor Name and 1 AM/PM ect. Without this being done im not sure i can help. But be patient there are tons of experts on here.

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Looking for some guidance on how to count entries in cells


    I can always change the way I enter the data. Is there a way to make one cell actually have like 3 entries but still look like one cell? Maybe I will tweak the workbook a bit and merge some cells and repost when I'm done. Might make things easier as more come on to add their two cents. Ill repost in a while.

  • Re: Count entries in a list and prevent duplicates


    I believe with an array formula you can have 1 cell look for and add your points. But a formula cant look for AM or PM or a Dr in 1 cell. See what i mean?

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Count entries in a list and prevent duplicates


    Hmmm...yah I see what you mean, I just don't know what to do. I am re-writing the entire workbook and have added a tab that lists all possible combinations of Doctors. Each Doctor has an option of "-am", "-pm", "-Princeton", "-Lakes", etc etc. maybe having that tab will allow for someone here to brainstorm how to get an array to look in the cell for any matching strings. Once matched, it can pull the value form the new tab and add up the collumn like you did earlier for a grand total. If the total is not "23" it makes the range background red or something. Anyone have ideas?

  • Re: Count entries in a list and prevent duplicates


    Get the sheet rebuilt and repost it with populated data. Then everyone can see exactly whats needed. I believe this can be done with only one formula but dont qoute me lol

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Count entries in a list and prevent duplicates


    Mike,

    If you are in the process of building this workbook then I suggest that time taken to read Excel/VBA Golden Rules, and the additional links, would be time well spent.

    As a quick suggestion though I would recommend that you build a separate sheet containing the raw data and then a 'Pretty Sheet' that extracts that data and presents it in the multi-coloured and formatted form you have in your examples.

    The raw data sheet should be unformatted text and as yegarboy was suggesting should split the data into columns rather than have it concatenated into a single line of text.

    My suggestion would be to have columns as follows:

    Date Location Doctor AM PM Time Notes Other Notes

    Every row should be complete even though it means rows of repeated data for Date. Only the Notes fields should be allowed to be blank. Use TRUE/FALSE for AM and PM. Since I noticed that some of your entries had both -am and -pm I would recommend that you have separate columns for these. When a doctor does both (no entry in your current system) then you fill in both AM and PM as TRUE. Time Notes would be used for situations when you have qualified the -am or -pm (eg -3pm). Other Notes are for entries such as: NO CT/GC.

    Setting out your original data this way will make performing your calculations much simpler. Also if your data entry requirements change, it is easier to modify the workbook with less impact. Similarly, by extracting information from the raw data and formatting it separately if the visual requirements change then that is unlikely to require changes to the raw data.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!