Finding data sequentially

I have a month of dates across columns by patient (in rows). In each
of these rows there is one date where a test was cancelled. Is there a
reasonable way to determine where the cell(s) that have the word
"cancelled" are relative to the numerical result(s) are in the month?
I'm trying to diffetentiate reorders that are caused by cancellations
from reorders due to patient condition. I realize that this will not
answer the question, but I can break out into two groups if I can tell
which came first, the result or the cancellation
0
jimboliz (14)
12/28/2010 6:47:43 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
621 Views

Similar Articles

[PageSpeed] 14

On Dec 28 2010, 1:47=A0pm, jimbo <jimbo...@gmail.com> wrote:
> I have a month of dates across columns by patient (in rows). In each
> of these rows there is one date where a test was cancelled. Is there a
> reasonable way to determine where the cell(s) that have the word
> "cancelled" are relative to the numerical result(s) are in the month?
> I'm trying to diffetentiate reorders that are caused by cancellations
> from reorders due to patient condition. I realize that this will not
> answer the question, but I can break out into two groups if I can tell
> which came first, the result or the cancellation



Hi jimbo:

Here is a typical way to find the date associated with the occurance
of some text.  Say we use col B for patient names.  In C1 thru AG1 we
enter the monthly dates:
1/1/2011	1/2/2011	1/3/2011	1/4/2011	1/5/2011  etc.

For example, B2 may contain "James" and I2 may contain the word
"cancelled"
In A2 enter:
=3DOFFSET($C$1,0,MATCH("cancelled",C2:AG2,0)-1) and format A2 as a Date
This displays 1/7/2011

The formula looks across the row to find "cancelled" and then looks up
to the first row to retrieve the date.

An error means that there was no "cancelled in the row>
0
1/1/2011 12:22:46 PM
Reply:

Similar Artilces:

Tally Position of Data
Hello, Sorry for the strange way I have labelled the 'subject' but that is all I could think of. I wish to return the position of a person in a list. That person's position will change daily by the introduction of points gained. There are 3 columns: Column 1 has the persons name Column 2 has the points gained Column 3 is the current position The table is below Col A Col B Col C Name Points Position Tom 40 Dick 50 Harry 25 What is the formula to produce Name Points Position Tom 40 2 Dick 50 1 Harry 25 3 Thanks if yo...

How to extract the same data from different sheets
Hi, I need to extract the common and same data from four different sheets into one sheet. Could you please let me know how to do it? I can use VLOOKUP, but I need to generate one temperary sheet for any two sheets. And based on these temperary sheets, I need to use VLOOKUP to generate other temperary sheets. And then finally I could get the last result. Too complex. THanks. --Kening Can you say more about the structure of your data, how you are identifying "common and same data" and why you need all those temporary sheets to use VLOOKUP? Jerry program_info@yahoo.com wrote: ...

Data format in publisher merge function
I am trying to merge data from excel to Publisher and some columns of data come across with too many decimal position i.e number in excel 54.090 comes across as 54.09000000003. I have tried to format the columns as text but does not seem to work. DK <DK@discussions.microsoft.com> was very recently heard to utter: > I am trying to merge data from excel to Publisher and some columns of > data come across with too many decimal position i.e number in excel > 54.090 comes across as 54.09000000003. I have tried to format the > columns as text but does not seem to work. Export ...

Excel data search help
I am asking for some help I have a sheet which has full details of all the calls this month to the helpdesk I have a second sheet which has full details of all the systems on contract and service pack I need to find all the calls logged by contract customers and sp and split them out for my boss The only key that I can use (from what I can see) is the serial number of the machine as it appears in both sheets and is unique I am not sure how to do this can you help? Thanks alot Depending on how your data is laid out, you might be able to use VLOOKUP for this - the sought item needs to be ...

Importing private calendar data to a public calendar
Hi, I have exported a private calendar to a PST file and I'm trying to import it into a public calendar I have created. I get a message saying "You have chosen to import a folder whose type differs from that of the selected folder. The items will be imported into a new folder with the same name." However it doesn't import it to anything I can find. I am able to import the data into another private calendar though so I no the data is good. Any suggestions on how to transfer private calendar data to a public folder calendar ? Thanks, Tony Tony Tuccillo wrote: > H...

Copy a column and paste to another tab to save the data for each m
How can I copy a column B10:B70 a spread sheet have 60 rows of calculation and I want to I click save button it will copy and paste just a value to separate tab name Estimate for each month . It need to check on cell B6 if it a month 1 or 2 or 3....to 12 so it will copy and paste data to a correct column for each month. thanks Liem Pho If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you...

How do I retrieve data from 1 hour ago?
I have created a large data base and was sorting into categories. I have sorted some of the data without names and want to retrieve the data base from 1 hour prior to realising my mistake. Am I able to do this? I am using Vista Basic exel. Go to your backup copy, if you saved the wrong version only once. -- David Biddulph "Dodgers" <Dodgers@discussions.microsoft.com> wrote in message news:40AD3854-4E6B-47D7-B839-4B4A5C69CB24@microsoft.com... >I have created a large data base and was sorting into categories. I have > sorted some of the data without names a...

Migrate Contact Address Data from CRM 1.2 to 3.0
We are upgrading from CRM 1.2 to CRM 3.0. In order to migrate the existing data, we are using the CRM web services (in "../crmservice.asmx"). CRM 1.2 stores the contact data in the ContactBase table; I create a data source, create an instance of the "contact" object from the CRM web service, set each of the contact properties from the field values in the DataRows of the DataSet source, and then use the contact object's Create() method to add each contact to the CRM 3.0 database -- all of this works very well. However, CRM 1.2 stores the contact's address d...

Exchange 2003: Mailbox size vs exchange data file on disk?
I've just done a quick adding up for all my mail boxes on my exchange 2003 server, and it totals around 3.7GB However, the datafiles on disk are: priv1.edb - 4.8GB priv1.stm - 2.8GB What makes up the rest of the space used? I know there are a couple of address lists and 2 public calendars, but that woudlnt even be a hundred meg, let alone a gig! Any ideas? is this normal behaviour? Thanks :) Some items that take up space are Deleted retention items, deleted mailbox settings and deleted public folder retention. Also the database size does not shrink, so there may be empty space in ...

Find Value; Return Row number
Goodmorning everybody! I was wondering if someone could help me with the following problem: "I wanted to make a macro which would find the first value (counting from a1) that matches the value stated in C14 of "FORM". When it gives a match I want to return the row number to "Sheet2". I tested it with dates of which I was certain it had to find a match (as I entered them myself in "Sheet1") Still it returns: "Named Argument not Found" (Runtime error 448)" I hope someon sees my mistake(s). Thanks in Advance!! Sub TestDelete() l = Sheet...

Excel Chart auto data highlighting anomaly....
Hi, Over the years I have created many excel charts. Sometimes I will just select the data range, and then click the chart button; sometimes I just start with the chart button. Some of my charts have what I call AUTO HIGHLIGHTING. When I click a blank area in the chart (line charts, embedded in the sheet), the X & Y data ranges are automatically highlighted in blue and magenta. If I click on one highlighted column and drag to expand the range, the other automatically follows. I have many other charts, created the same way, that do not exhibit this behavior. On t...

How to find Latest n files from a directory using VC++
Dear Experts , I am developing an application in VC++ 6.0 . My application is releated with reading and writing large numbers of file . i have one directory say [C:\Test] which contains 10,000 files and may be more . i want to know how can i find latest 20 files that is created in C:\Test . I am using CFileFind and CFileStatus to find file and to Get Creation time of file but i think as my content of directory increases it will comsume more time for searching latest 20 files ...... Can anyone tell me how to come around this searching problem... is there any readymade class available th...

Find/Replace Question
This happens almost everytime I use Find/Replace, but I just deal with it. Maybe you guys can set me straight on this. I have the following formula: =SUMPRODUCT(--(MOD(ROW($AB$14:$AB$1008)-ROW($AB$14),4)=0),--(ISNUMBER($AB$14:$AB$1008)),--($AB$14:$AB$1008>0)) I want to replace all of the ABs with AK. So what I do is highlight the formula, (Excel 2007) go to Home>Find and Select>Replace> Find what: AB Replace with: AK Replace>Close However, after I have done that I have to hit enter, and then I have to hit UNDO to get the desired result. There must be so...

Report Data not being sent to printer
Hey all, I have created a report that mimics an order form. to fit a normal sized paper, blank rows are added to the report so that it looks like an actual order form, instead of an order form with only 3 entries. it looks great when its printed onto the screen, but it does not print physically to the printer or even to pdf. any ideas to what could be going wrong? Do you have any code you could share with us? What makes your preview display extra lines? Are we supposed to know your method without you providing some details? -- Duane Hookom Microsoft Access MVP "tech.rawsteak@gma...

QUICKSELL Blank or Empty Fields/data
How should I treat an empty excel field on import with Quicksell? Example "Notes" <I don’t' have any data for this field>. Do I leave the field blank, or place a comma in the field, or put some text, or delete the column and don't include the field in the import? How should I treat "Last Sold" field if I do not have a history? Similar to above do I leave the field blank, or place a comma in the field, or put some text, or delete the column and don't include the field in the import? When I have a item unlike others with data what should I do if I ...

High Low Lines or Delta between two data sets
I have two data sets I am tracking against each other and I would like to be able to display the delta between the two lines. the high low lines would be great if you could add a delta number to them but I don't think that is possible. is there some other functionality that will give me this? Example if one value is 5 and the other is 8 I would like a line between them with a 3 on it. Any Ideas Thanks -- Jeff Hi, You can create this chart appearance. http://www.andypope.info/charts/Hilolabel.htm Cheers Andy Jeff wrote: > I have two data sets I am tracking against each oth...

Excel, DB data and RTF
Good morning to you all I have a sql server table in which there's an attribute containing an rtf string; something like {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Verdana;}} \viewkind4\uc1\pard\ul\b\i\f0\fs32 pippo\ulnone\b0\i0\f1\fs18 \par } Well, using a query to an external source I get the string, but... exactly as you read it here above. I do need to transform it in something readeable. I'd love a function like: "=GetRTF(A1)" ;) but I'm not able to find it and at the same time I cannot f...

How to extract outlook calendar data.
Hi, I want to extarct outlook calendar data ( i.e the data of all the shared calendars). Can anyone let me know where does the data stored and how to extarct the same. Thanks and Regards, Sindhura Your calendar data is stored in either your pst-file or on the Exchange server. How to extract it depends on which format you wish to convert it to, what you want to do with it and which versions of Outlook you are using. For general instructions see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft ...

connecting to CRM data
i wish to create custom .aspx files with C#, that will be integrated into the CRM app, that retrieves and edits data from CRM tables. I have no idea how to connect to the CRM data and cannot find any enlightening information. Can I connect directly or will i have to use an intermediate database that connects in via the cdf database? slightly confused!! try to google for: sdk crm you surely will get to the official SDK and some examples.... "java.equals(cool)" <java.equals(cool)@discussions.microsoft.com> schrieb im Newsbeitrag news:5F0F2162-665D-4EFE-9149-7D6218A07...

suddenly cannot write new message. server cannot find send/receiv
all of a sudden i cannot write new message. a warnige comes up informing me send/receive cannot be found. Have i deleted something inadvertantly? ...

Able to find text properties?
Is there a function in excel to test the text properties of a cell. ie. if it's bold or red color text? thanks naveed010@hotmail.com G0 to the Help files, and lookup Cell worksheet function That will give you a list of the types of formats that can be returned. Mostly date, time and currency formats. The only color return would be the "negative red" in currency. What you're looking for needs code. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ========================================...

Data Migration Exam
Hi all,,, Anyone got any good references for the Data Migration aspect of this exam, did the 1.0 course and have that course material but it does not include the DM, nice supprise that one when I sat down for the exam the 1st time, I have tried the documentation that comes with DM and have failed a 2nd time having not understood a thing that was going on and drawing a blank.. Idiots guide would be nice but anything else appreciated. Jay Have you read the Data Migration Framework documentation? It is a very thorough guide to the entire process of DM. See my later posting re Exam Cram ma...

Unable to find WAB.DLL ????????
Having a problem with Outlook. Can't send out emails from my outlook .... Message :"Unable to update public free/busy data. Unable to find WAB.DLL." -- Trying to determine the structure of a protein by UV spectroscopy was like trying to determine the structure of a piano by listening to the sound it made while being dropped down a flight of stairs Post your version. No current versions of Outlook use the WAB. Why don't you turn off the update free/busy option? -- Russ Valentine [MVP-Outlook] "harry palmer" <rob@aol.com> wrote in message news:QVg3e.2...

Convert vertical data to horizontal data
I have a listing of 6 performance ratings for each employee. The ratings appear vertically. How do I convert the ratings data to a single row for each employee? Copy - paste special - transpose -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kiley" wrote: > I have a listing of 6 performance ratings for each employee. The ratings > appear vertically. How do I convert the ratings data to a single row for > each employee? I think I need more information. I tried what you suggested but I got the following me...

Using VLOOKUP to find colours
Hello, I want to use a VLOOKUP to pull information out of a chart depending on what colour the cell has been filled in. The Look_Up value needs to relate to the colour but i do not know how to force it to do this. Any help would be appreciated. I think you are pretty much forced to write a VBA macro because I am not aware of worksheet functions that return either the colour of a cell or the text it contains. In VBA there is ActiveCell.Font.ColorID for example. The nearest workaround I could come up with, is to use a cell adjacent to your data to store the...