listing and alphabetizing patient names always populates 1 less patient than what I have

**opusarlo** · 04-28-2015, 09:37 PM

On the orange tabs ("Meals" and "Vitals" tabs) you will see some columns dedicated to the alphabetizing of patient names grayed out. In the blue column you can see how the patient name is populated in alphabetized order and truncated to the first letter of the last name. If you count the names in column A and compare with the names in column F you will always be one short. Why is this? You can plainly see the formulae are functioning, and there are not syntax errors...Can anyone shed some light on this for me?

BADRED33desensitzed.xlsm

**Shareez Saleem** · 04-28-2015, 10:00 PM

Hi,

As per the last criteria on your formula "=IFERROR(LEFT(C8,FIND(" ",C8))&" "&MID(C8,FIND(" ",C8)*1,2),"")" if the cell value of "C" is blank, the value should be blank.

Kindly double check your data.

Regards
Shareez

**GC Excel** · 04-28-2015, 10:03 PM

Hello,

I've never been a big fan of this kind of formula... Looking at the example you sent, seems like "big wheel" is missing.
I believe it's because "W"heel comes after "V" in #Value.

Try replacing your formula in B8 by :
=IFERROR(TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),255))&", "&LEFT(A8, LEN(A8)-LEN(TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),255)))-1),"ZZZZZZZZZ")

Instead of #VALUE, you'll get "ZZZZZZ" but this seems to fix the problem.

(Now, don't ask me why... I'm never able to figure out this formula and will prefer the VBA approach).

**sktneer** · 04-28-2015, 10:25 PM

On Meals Log
In B8

Please Login or Register  to view this content.

and copy down.

Then in C8 try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

Please Login or Register  to view this content.

and then copy down.

************************************
Remember an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**sktneer** · 04-28-2015, 10:29 PM

I think I have missed the sorting part.

**opusarlo** · 04-28-2015, 10:38 PM

GC excel I am glad to see I am not the only one who pulls hair out with this thing. Thank you so much for a solution...I have no idea how, but it works. Amen!

listing and alphabetizing patient names always populates 1 less patient than what I have

LinkBack

Thread Tools

Rate This Thread

Display

listing and alphabetizing patient names always populates 1 less patient than what I have

Re: listing and alphabetizing patient names always populates 1 less patient than what I ha

Re: listing and alphabetizing patient names always populates 1 less patient than what I ha

Re: listing and alphabetizing patient names always populates 1 less patient than what I ha

Re: listing and alphabetizing patient names always populates 1 less patient than what I ha

Re: listing and alphabetizing patient names always populates 1 less patient than what I ha

Thread Information

Users Browsing this Thread

Similar Threads

How to count number of different dates for a patient.

apply textbox input to selected cells

Problem with signs on budget percent variance formula

Averaging Averages For Patient Care

[SOLVED] I need to exclude duplicate patient names for dates of service is.

Bookmarks

Bookmarks

Posting Permissions