Mark Duplicate Referrals Which Occur With 30 / 60 / 90 Days For Same Medical Condition
Feb 26, 2014
Attached is an example of an excel spreadsheet I have which details all the referrals created within my organisation over 2013. I want to be able to create two excel formulas which basically looks at a duplicate tag and then determines if the duplicate referral was created within 30 days of other referrals created for the same patient and then another formula to check to see if the duplicate referral is for the same medical condition. Also if possible I would love a third formula which would say check duplicate, 30 days and same condition and enter a "Yes" in a forth column.Referrals.xls
View 8 Replies
ADVERTISEMENT
Mar 31, 2014
I wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.
Attached is an example referral spreadsheet I have created
View 4 Replies
View Related
Jun 12, 2008
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
View 3 Replies
View Related
Jan 30, 2008
Excel 2007 User
Colum b:b I have values "x", "a", "b" and so on ....
But some time value x is on row 10 and row 2000
I want to mark that row on a color or something
Example:
Like a loop, So I found ...
A on cell b2
A on cell b24
View 9 Replies
View Related
Nov 15, 2006
I am trying to create a column to match duplicate information, primarily numbers, so I can delete the duplicate information. Using the Find option is too time consuming. I am pasteing the new info above the previous weeks info. Some years ago I used a formula that would "Match" a range and I believe it would copy the duplicate in the empty column but it's been so long I am not certain nor do I remeber the formula. I've tried using the filter feature and looked at previous post but I am not familiar enough to know how to decipher the info given.
View 9 Replies
View Related
Mar 19, 2009
I have a approx. 70 excel sheets with thousands of entries. Within a single sheet there are some duplicate entries (based on an account number). I find the duplicate entries by using the excel 2007 conditional formating and then sort by color to only show the duplicate entries. There are often hundreds of accounts that are duplicates. Sometimes there are more than 2 identical account duplicates (could be 3,4 or more). In each entry there is a number representing the rating of said account. These numbers differ within the duplicates. Example............
I am trying to find a formula that will choose a "winner" and enter a "W" based on the highest rating within the duplicate entries and then ideally assign an "L" to the loser duplicate account(s). Sometimes there is no rating, other times the rating could be the same in which case any account (does not matter) can be assigned a "W" and the others an "L"
View 3 Replies
View Related
Jan 6, 2010
If H6 amount is greater than E8 then the answer in F8 will be the same as H6. If not, F8 will be the difference between H6 and E8.
The above will be used for the deductible amount as follows:
If patient A has a $1,000 deductible and the allowed amount for the product is only $250.00, then all of that woudl be the patient responsibility. However, if the patient has a $250.00 deducble and the allowed amount is $1,000 then the patient would only owe the $250.00 deductible.
Is there a formula that can decipher this?
View 9 Replies
View Related
Feb 2, 2014
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
View 3 Replies
View Related
Aug 20, 2008
Wondering if there is a way, script-wise or formula-wise that will look through a table and find duplicates but not just any duplicates, it has to match a certain criteria.
For example, I have a column for UPC and Vendor#. I have my table sorted by vendor (all 159 of them in one table) I want to find out whether a UPC appears more than once for a vendor.
UPC --- Vendor
11254 135
11251 135
11251 135
11254 9345
11251 9345
Here, as you can see, UPC 11251 appeared more than once for vendor 135. Can this be done through conditional formatting ? or a script?
View 9 Replies
View Related
Nov 1, 2006
how to delete duplicate rows except the first and the last rows using macro..
I have data like this and i want to delete those rows except the first and the last rows
31/10/2006
31/10/2006
31/10/2006
31/10/2006
I use the below macros from btadams posted 27th January 2003 but only for delete the last row
Sub DleteDups()
Dim Cell As Range
Do While ActiveCell.Offset(1, 0) <> ""
If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If
Loop
End Sub
View 9 Replies
View Related
Jun 4, 2014
My data set has a number of duplicate entries. But I would like to sort them out based on some conditions. Say for example my C2 code appears three time in the dataset. I would like to sort this multiple code using the time and i column.
Say for example, 871514 code appears three times in this dataset, and if this code appear within three years - time with a different (i column text e.g. public and private), I would like to exclude these three duplicate entries from my dataset. If this code appears within next three years from the date, but have same I column text (e.g.public versus public), I will keep them.
So each code will be considered based on three years of time and type of text in I column. If the code appears again later after three years, I will keep them.
So the codes need to be checked within three years time with i column text.
My dataset identifies the difference of dates between last entry and next entry.
View 1 Replies
View Related
Jan 16, 2009
I got this code form Ozgrid that works great other than everytime I run the macro it copies everything over again.
View 3 Replies
View Related
Oct 31, 2012
Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.
View 6 Replies
View Related
Dec 19, 2008
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies
View Related
Mar 14, 2008
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.
E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1
View 13 Replies
View Related
Jan 13, 2010
I would like a formula (if it is possible) that will list which months occur between two dates;
i.e
Start Date (Cell ref A2) = 01/01/2010 (in the dd/mm/yyy format)
End Date (A3) = 02/05/2010
In cells D2:O2 I have the months Jan-Dec. In cells D3:O3 I would like a "Yes" to appear if the above month occurs between the dates in A2 & A3. In this example would like a "Yes" to appear in cells D3, E3, F3, G3 & H3 but not in the other 'Months' appropriate cells.
View 2 Replies
View Related
Aug 19, 2012
In a cell i need this info when
column a = month
column o = staff member
column m = discount given
if no discount is given column m will show 100%
i need the total of all sales made with a discount i.e not 100% and not blank, in a certain month, by a certain member of staff
step 2: i need the average of all these for each member of staff shown in a different cell
i already have the total sales counted per staff member so this will show me who is doing deals and who is doing the biggest deals.
View 4 Replies
View Related
Sep 19, 2007
i need to delete rows from sheet2 which contains source data for Vlookup on sheet1.
that is i need to run a vlookup for a value in sheet1 and after the value is obtained , delete the row which has that value in the source data......
i am sure a simple for loop will help but it needs to do the events in sequence:
vlookup for row1> fetch / place value (sheet1) > delete data in the source table (worksheet2) > go back to row2 and repeat till the the last.. or till the end of a predefined range..
View 9 Replies
View Related
Feb 26, 2009
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.
View 6 Replies
View Related
Mar 15, 2014
I'm trying to create a master template for doing cash flow projections for a number of years. Ideally I would like have a cell for the number of years I want (10 for example) and have excel populate a group of cells based on that number. Each row of cells would be one year and each column would be one month. The dates would be calculated based on a starting date that I enter into a cell.
After that I would like to have a cell for starting cash flows ($1,000 for example) and then another cell that I can plug increases into. For example cash flows may increase to $1,250. I would also need a cell for the date cash flow increases go into effect (1/2019 for example).
View 1 Replies
View Related
Aug 4, 2014
I am a behavior specialist and I have created a spreadsheet to determine what possible antecedents trigger certain behaviors. In other words, I want to know what occurs, like being around an unfamiliar person, right before a person engages in an "acting out behavior" like becoming verbally or physically aggressive. In my spreadsheet I have a number of possible antecedents with a number of possible behaviors. It is tracked by the date that the behavior occurred.
View 6 Replies
View Related
Jul 24, 2009
Basically my sheet links may appear as a cell in another tab or it might be to an http address... Here is my formula I have setup up right now.. Only problem is, no matter what I put into the logical test to get a "true" result for the http addresses it still says false..
View 2 Replies
View Related
Nov 20, 2013
I have a spreadsheet with over 15 columns showing drivers (names numbers etc) and their duties with more inf.
What I need is to be able to find when a certain type of driver in column A (drivers belong to different rutes) say from route A is at rest (this is shown as RD) which is shown in column B and then be able to count how many times those two exact events occur.
View 1 Replies
View Related
Jan 14, 2013
I am trying to determine whether or not 26 blanks occur in succession in a row. Two example rows of data are listed below, where "_" refers to a blank cell:
2 _ _ 3 _ _ _ _ _ _ _ 4 5 6 3 4 _ _ _ _ _ 4
_ _ _ _ _ _ 7 8 13 2 3 _ _ _ _4 5 6 11 10 2
Hopefully you can picture the data from above. Anyway, there are multiple locations where a string of blanks occur. I'm looking for a formula that can test as to whether or not a certain number of blanks occur in succession during any part of the row.
View 10 Replies
View Related
Oct 27, 2008
I am sure that most of you will probable know what i am doing wrong here straight away so rather than me waste another 1/2 hr stuffing around. I am trying to find count the number of times two events occur simultaneously.
=SUMPRODUCT(--('5. Financial'!AX4:AX134=A5)*('5. Financial'!BQ4:BQ134>0<0.1))
The first part is fine I think but in the second part I am trying times only when cell is between zero & 10%.
View 3 Replies
View Related
Jan 25, 2012
I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)
So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?
View 1 Replies
View Related
Jan 18, 2014
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....
View 5 Replies
View Related
Sep 25, 2007
The file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).
In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$.
The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.
Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.
It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value
View 7 Replies
View Related
May 5, 2008
I will say exactly what i want.
I want to keep statistics of roulette.
More specifically, i want to count how many times a dozen lates to come.
The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.
I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.
For example: I have put in a column 500 numbers from 0 to 36.
The output must be like this:
1st Dozen: Every 1 time: x
Every 2 times: n
Every 20 times: m
The same for 2nd and 3rd dozen.
View 9 Replies
View Related
May 27, 2012
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
View 5 Replies
View Related
