Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hello,
I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
How does 'best and latest' for each day translate into a No or Yes? That
seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Duke,
I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Ok, let's recap -
You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Send me a sample file (remove TRUENORTH from my email address)
It is my understanding that "Eskimo" is no politically correct these days, so I send best wishes to Inuit. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Eskimo" wrote in message ... Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Duke,
I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Can you just import into Access? Yes, but if the data is constantly added to
the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Duke,
I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Bernard,
Your right, the word Eskimo is not politically correct. Me being one, it don't bother me one bit. I am not very political and not easily insulted by such a name. :-) I have sent you an example of the excel file by Email. Jonathan "Bernard Liengme" wrote: Send me a sample file (remove TRUENORTH from my email address) It is my understanding that "Eskimo" is no politically correct these days, so I send best wishes to Inuit. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Eskimo" wrote in message ... Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Sorry Eskimo -
Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Duke,
The code worked but with an exception. It did give me the highest ranked collar for each collar for each day, but it also gave me all of the same ranked collars on the same day on a previous time. I think we can run another query to remove the previous record(the same collar, on the same day with the same rank, but on a previous time). I will accept your suggestion now and thanks very much for your time. Eskimo "Duke Carey" wrote: Sorry Eskimo - Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Try this modification:
SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND (C1.Collar=c2.Collar) AND (c1.Date=c2.Date) GROUP BY c1.Date, c1.Collar, c1.LC; "Eskimo" wrote: Hi Duke, The code worked but with an exception. It did give me the highest ranked collar for each collar for each day, but it also gave me all of the same ranked collars on the same day on a previous time. I think we can run another query to remove the previous record(the same collar, on the same day with the same rank, but on a previous time). I will accept your suggestion now and thanks very much for your time. Eskimo "Duke Carey" wrote: Sorry Eskimo - Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Works like a charm. Excellent work. Your the best.
I had this same question posted in Access portion of this newsgroup. I'll post a thank you in that area direct to you also. Thanks again. Eskimo "Duke Carey" wrote: Try this modification: SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND (C1.Collar=c2.Collar) AND (c1.Date=c2.Date) GROUP BY c1.Date, c1.Collar, c1.LC; "Eskimo" wrote: Hi Duke, The code worked but with an exception. It did give me the highest ranked collar for each collar for each day, but it also gave me all of the same ranked collars on the same day on a previous time. I think we can run another query to remove the previous record(the same collar, on the same day with the same rank, but on a previous time). I will accept your suggestion now and thanks very much for your time. Eskimo "Duke Carey" wrote: Sorry Eskimo - Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
Hi Duke, I was wrong, it did the same thing and included the same collar on
the same day with the same rank but with an earlier time. hmm. tough one eh? I'll try and work it out, if you have any further suggestions, that would be great. Thanks, Jonathan "Duke Carey" wrote: Try this modification: SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND (C1.Collar=c2.Collar) AND (c1.Date=c2.Date) GROUP BY c1.Date, c1.Collar, c1.LC; "Eskimo" wrote: Hi Duke, The code worked but with an exception. It did give me the highest ranked collar for each collar for each day, but it also gave me all of the same ranked collars on the same day on a previous time. I think we can run another query to remove the previous record(the same collar, on the same day with the same rank, but on a previous time). I will accept your suggestion now and thanks very much for your time. Eskimo "Duke Carey" wrote: Sorry Eskimo - Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formala to find the best/latest signal
My wrong again.
It did work exactly as I wanted. It caused an error when I tried to include the other columns of information relevant to the table. It would just keep adding all values within the original table. Thanks again, I'll keep plugging at it. Eskimo "Duke Carey" wrote: Try this modification: SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND (C1.Collar=c2.Collar) AND (c1.Date=c2.Date) GROUP BY c1.Date, c1.Collar, c1.LC; "Eskimo" wrote: Hi Duke, The code worked but with an exception. It did give me the highest ranked collar for each collar for each day, but it also gave me all of the same ranked collars on the same day on a previous time. I think we can run another query to remove the previous record(the same collar, on the same day with the same rank, but on a previous time). I will accept your suggestion now and thanks very much for your time. Eskimo "Duke Carey" wrote: Sorry Eskimo - Try this one instead SELECT c1.Date, c1.Time, c1.Collar, c1.LC FROM Collars AS c1 inner join (SELECT Date, Collar, Max(LC) as MLC from Collars group by Date, Collar) as c2 ON c1.Date = c2.Date AND C1.Collar = c2.Collar AND c1.LC = c2.MLC "Eskimo" wrote: Hi Duke, I tried running the query as you described, however when I clicked on run, a message prompt appeared saying that "at most one record can be returned by this subquery" When I clicked on help, it says. a subquery of this kind cannot return more that one record. revise the select statrment of the subquery to request only one record". Any thoughts? Eskimo "Duke Carey" wrote: Can you just import into Access? Yes, but if the data is constantly added to the Excel sheet then linking is better. The query of a linked sheet will pick up any new data; otherwise you need to re-import each time you want to run the query on new data. The query I gave you will work just fine for the situation you have described "Eskimo" wrote: Hi Duke, I have several columns of data, but your right, there are four columns that we need for the purpose of this newsgroup question. I can combine the date and time to use as a single value. They come separate into the excel file so I just keep them that way. I also need the date value as part of the end table/excel sheet report. To further re-cap, I have about 30 different collars, each collar has several rows of "fix" positions so there are also several rows of the same collar. Within each group of rows of the same collars, there are several dates. Within each collar/date group, there are several ascending times. Now, each row has an "LC" (acronym for Location Class). LC being the quality of the position fix. 1 being lowest - 7 being highest. The thing to think about is that each collar's group which report on the same day has varying LC quality throughout the day. The best LC for that collar on that day could come before the last report went through on that day!. I'll try your Access suggestion, I am very familiar with Access and can link the excel data. can I not just import the excel sheet into a new collar named Collars? Thanks, Jonathan "Duke Carey" wrote: Ok, let's recap - You have 4 columns of data: Date/Time/Collar#/LC (any reason date & time are not kept as a single value?). For any given collar you can have multiple readings per day, and you want to find the time for the highest LC reading (for each collar) each day, right? This is much better handled in a database. Do you have MS Access? Do you know how to link from Access to an Excel file? The Excel file is best laid out with the column names in row 1, starting in col A and the data starting in row 2. Nothing else should be on the sheet. In Access use this query (assumes you name the linked sheet Collars, and the columns are named Date/Time/Collar/LC) SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC FROM Collars WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date))); "Eskimo" wrote: Hi Duke, I guess that it does not have to be a yes or no, that is how I used a formula in the past to find the best/latest day. How can I explain this. Each collar makes a position "fix" everyday. Each Fix's quality is not the same, so that in any one day, the fix can range from 1 - 7 with 7 being the best signal. However, there can be more than one fix with a 7 signal. There is also a time column, which I completely forgot to include in the original information. Forgive me. So the formula should decipher which of the fixes is the latest-best signal. It should mark a 0 for all the lower rank or earlier best, and mark a 1 for the latest best row for that particuliar collar on that particuliar day. Thanks, "Duke Carey" wrote: How does 'best and latest' for each day translate into a No or Yes? That seems a tad contradictory "Eskimo" wrote: Hello, I have a series of rows 14000+ lines strong in an excel sheet. The series contain caribou location reports from those caribou that have a collar attached to them. Along with other information, each row contains three important columns related to this question. Collar Number, Date and LC (LC = Position Fix quality or Signal Strength). I need a formula that will find the best and latest signal strength for each collar and for each day!. The formula should result in a 0 or 1. (no or yes). There are about 30 different collars each reporting on certain days. The LC values are 1 to 7 with 7 being the strongest. If anyone can find me a solution, that would be just excellent. Thanks in advance, Jonathan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
Edit + Find | Excel Discussion (Misc queries) | |||
can't update links...can't find links | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions |