Sequencing

**hektisk** · 01-31-2011, 01:13 PM

Hello:

I'm designing a client and statistic tracking sheet for a home care agency. Each new "start of care" - simply the start of service - is assigned a number (called a client number, though the same client may have multiple numbers if they've had multiple start of cares). For reimbursement purposes, home care services are provided in 60-day episodes. If at the end of a 60 day period a patient continues needing services, they are re-certified for another 60-day episode. There is no limit to the number of subsequent episodes. Episodes are numbered sequentially: episode 1, episode 2, episode 3...

I have columns labeled 'Client #' and 'Episode #'. What I'd like to do, if possible, is to have Episode # look to see if the Client # in the adjacent cell has been used before,
and, if so, to find out the most recent episode # and add 1 to it. If it helps any, there is a column of episode start dates, so I suppose an approach could be to add 1 to the episode number with the most recent episode start date.

Thanks in advance.

Adam

**MarvinP** · 01-31-2011, 01:52 PM

Hi Adam

Find the attached with the formula:

Please Login or Register  to view this content.

Here is how this works. The Dollar Sign makes all the difference
It counts all the Client numbers above that are equal to itself. It always starts at A$1 and goes down to the row it is in - no dollar sign on the row number.

See if this works for you.

**hektisk** · 01-31-2011, 02:44 PM

Thank you. This seems to work except when I go to add a new line to the table. As soon as I enter in an existing client number in a new row, the countif formula for the preceding row automatically adds another row to the array! Is there a way around this?

Originally Posted by MarvinP

Hi Adam

Find the attached with the formula:

Please Login or Register  to view this content.

Here is how this works. The Dollar Sign makes all the difference
It counts all the Client numbers above that are equal to itself. It always starts at A$1 and goes down to the row it is in - no dollar sign on the row number.

See if this works for you.

**MarvinP** · 01-31-2011, 02:52 PM

You have your range set to a Table, so Excel thinks you want to copy down the formula in column B.
To fix this (if that is what you want) Right click anywhere in the table and then Click on TABLE and Convert To Range.

This will keep Excel from automatically putting the formula in the next row.

**hektisk** · 01-31-2011, 02:54 PM

Also, I'm not sure if that formula would allow me to sort the data accurately as the reference is too dynamic, if that makes sense.

I'm attaching the workbook here - which I realize I should have done at the beginning

- so you can see exactly what I'm trying to do.

Nothing confidential here - all of the data is made up.

**hektisk** · 01-31-2011, 02:57 PM

I actually do want to keep the auto-copy down of formulas for the other columns. I attached the workbook to the last post - it's not fair to keep you guessing as to what I'm trying to do! Sorry about that.

Originally Posted by MarvinP

You have your range set to a Table, so Excel thinks you want to copy down the formula in column B.
To fix this (if that is what you want) Right click anywhere in the table and then Click on TABLE and Convert To Range.

This will keep Excel from automatically putting the formula in the next row.

**MarvinP** · 01-31-2011, 03:48 PM

Ok Adam,

Find the attached. Here is what happens. Read the code behind Sheet1.

Whenever you Type a number into Column A, the code will run through and see the largest number with that code and add 1 and put it in the Episode column B.

You will have to take all the existing formulas out of the Episoid column. I'd Copy and Paste (Values Only) over the top of the existing ones.

I'm having some concerns on what happens if you change a Client Number that is already there.

Play with this a while and let me know. It might need some more error checking - like only do the macro if you are typing on a blank client cell or somesuch.

Let me know if this works for you.

**hektisk** · 02-01-2011, 10:13 AM

Interesting! Thank you so much for your help.

Originally Posted by MarvinP

Ok Adam,

Find the attached. Here is what happens. Read the code behind Sheet1.

Whenever you Type a number into Column A, the code will run through and see the largest number with that code and add 1 and put it in the Episode column B.

You will have to take all the existing formulas out of the Episoid column. I'd Copy and Paste (Values Only) over the top of the existing ones.

I'm having some concerns on what happens if you change a Client Number that is already there.

Play with this a while and let me know. It might need some more error checking - like only do the macro if you are typing on a blank client cell or somesuch.

Let me know if this works for you.