I have a series of patient identifiers. For many there are multiple rows for the same patient. Each row has a date, so Id like to create a new column which will be a number corresponding to rank in time for that row. For some patients there would only be one entry (row) which will be a 1. For others there will be multiple rows and id like them to be to ranked 1-10 (or however many) according to time, ie. first entry 1, last entry 10.
Will you please attach a sample Excel workbook? Please don't attach a picture of one.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I'm not sure what you mean by ranked in time... Can you explain.
This array formula will return a list of unique patient IDs (in E2, copied down):
=IFERROR(INDEX($A$2:$A$30,MATCH(0,INDEX(COUNTIF($E$1:$E1,$A$2:$A$30),0),0)),"")
If what you want to do is count the number of visits (???), In F2, copied down:
=IF(E2="","",COUNTIF(A:A,E2))
Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
here is sample data and I have manually filled in column 3. So if we look at row 24, we can see that this is the 4th entry (by date) for patient a
many thanks
Use
=COUNTIF($A$2:A2,A2)
Have you made a mistake at C15 (should be 5)
Not working
'the formula in this cells refers to a range that has additional numbers next to it'
if i ignore this error nothing changes
if I click on a cell then press return it then recalculates and gives the correct result
bit odd
just clicked 'save as' and it took the opportunity to recalculate all the values correctly
so all sorted now
thanks Glenn
Check that you do not have formulas calculation options set to manual... It should be at automatic (normally...).
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks