| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
capturing all of the data
I have a list of people with test resullts. I need to identify the
infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. But I need all of the results of any individual with one or more positives. Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
Use autofilter and filter on positive. You can copy the results and paste
into a new sheet if you want. -- Biff Microsoft Excel MVP "jimbo" wrote in message ... I have a list of people with test resullts. I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. But I need all of the results of any individual with one or more positives. Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
Biff
I read it as OP wants all records for a patient including negative records if a patient has at least one positive record. Probably have to filter a couple times. Too late for thinking right now.............night all Gord On Sat, 26 Jul 2008 22:22:35 -0400, "T. Valko" wrote: Use autofilter and filter on positive. You can copy the results and paste into a new sheet if you want. -- Biff Microsoft Excel MVP "jimbo" wrote in message ... I have a list of people with test resullts. I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. But I need all of the results of any individual with one or more positives. Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
G'day Jimbo
Expanding on Biff's response, you can automate the process by using the code at the link below. http://www.rondebruin.nl/copy2.htm It will filter, then copy your filtered ranged based on your criteria and paste to a new worksheet for you. attach the code to a Macro Button on your WS. HTH Mark. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
On Jul 26, 7:22*pm, "T. Valko" wrote:
Use autofilter and filter on positive. You can copy the results and paste into a new sheet if you want. -- Biff Microsoft Excel MVP "jimbo" wrote in message ... I have a list of people with test resullts. *I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. *This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. *But I need all of the results of any individual with one or more positives. *Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative- Hide quoted text - - Show quoted text - That will get me all individuals with positive results, but will not include negative results. I need something along the lindes of any indivicual with both positive and negative results, and exclude all individuals with all negative results |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
On Jul 26, 8:46*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Biff I read it as OP wants all records for a patient including negative records if a patient has at least one positive record. Probably have to filter a couple times. Too late for thinking right now.............night all Gord On Sat, 26 Jul 2008 22:22:35 -0400, "T. Valko" wrote: Use autofilter and filter on positive. You can copy the results and paste into a new sheet if you want. -- Biff Microsoft Excel MVP "jimbo" wrote in message .... I have a list of people with test resullts. *I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. *This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. *But I need all of the results of any individual with one or more positives. *Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative- Hide quoted text - - Show quoted text - you are correct, only those with both positive and negative results |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
I think a helper column would help you here. If I understand, you want to
exclude anyone who only has negative results, e.g., count of positive results=0. If this is the case, add a column and use the following formula: I'll assume your first john smith appears in cell A2 and extends to row 10000. =sumproduct(--($A$2:$A$10000=A2),--($C$2:$C$10000="positive"))=0 This would give you a column of TRUEs and FALSEs. You simple filter out the TRUEs -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "jimbo" wrote: I have a list of people with test resullts. I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. But I need all of the results of any individual with one or more positives. Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
capturing all of the data
Try this...
Add a helper column with this formula: =IF(SUMPRODUCT(--(A$2:A$2000=A2),--(C$2:C$2000="positive")),"x","") Copy down to the end of data. Then filter on this helper column on "x". -- Biff Microsoft Excel MVP "jimbo" wrote in message ... On Jul 26, 7:22 pm, "T. Valko" wrote: Use autofilter and filter on positive. You can copy the results and paste into a new sheet if you want. -- Biff Microsoft Excel MVP "jimbo" wrote in message ... I have a list of people with test resullts. I need to identify the infividuals who are positive, and list all of their results, e.g. in the following example, I would want all of the results of john smith and mary kay because they have one or more positive results, but not any of the results of jim jones or susan rae, because all of their results are negative. This is easy enough when there is a limited number of individuals, but when it is in the 10s of thousands, it's impossible for me to capture anything more thatn the positive from a single date. But I need all of the results of any individual with one or more positives. Any suggestions? john smith 3/4/2008 negative john smith 4/4/2008 positive john smith 5/4/2008 negative john smith 6/4/2008 negative john smith 7/4/2008 negative jim jones 3/4/2008 negative jim jones 4/4/2008 negative jim jones 5/4/2008 negative jim jones 6/4/2008 negative jim jones 7/4/2008 negative mary kay 3/4/2008 negative mary kay 4/4/2008 negative mary kay 5/4/2008 negative mary kay 6/4/2008 positive mary kay 7/4/2008 negative mary kay 8/4/2008 negative susan rae 5/4/2008 negative susan rae 6/4/2008 negative susan rae 7/4/2008 negative susan rae 8/4/2008 negative- Hide quoted text - - Show quoted text - That will get me all individuals with positive results, but will not include negative results. I need something along the lindes of any indivicual with both positive and negative results, and exclude all individuals with all negative results |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Capturing The Date Of When Data Is Inputted | Excel Discussion (Misc queries) | |||
| capturing Data and printing line elsewhere | Excel Discussion (Misc queries) | |||
| Capturing Rows of Data | Excel Discussion (Misc queries) | |||
| Capturing data using a formula | Excel Discussion (Misc queries) | |||
| SUMPRODUCT CAPTURING DATA FROM ANOTHER SPREADSHEET | Excel Worksheet Functions | |||
Linear Mode
