Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code for any times within that range of time: 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 BUT, I need to be able to allow for multiple answers in one cell. Here's an example of what I need it to do: 12:00 AM 8:55 AM 1 8:55 AM 9:05 AM 1, 2 9:05 AM 9:55 AM 2 9:55 AM 10:05 AM 2 10:05 AM 10:25 AM 2 10:25 AM 12:00 PM 2, 3 12:00 PM 12:30 PM 3 This is for a research project that has do be done by Monday, can someone help? I'm mildly computer literate but would need mostly laymans language. HELP! -- Elizabeth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Presumably, you won't have any times before 5:00 am or after 7:00pm?
This is a nice little problem, but it's a bit late here in the UK so I haven't got time to delve into it. If you haven't had any solutions by the morning, I'll look at it then. I think you just need a simple table of times and codes, and an IF to give you the composite codes. Pete On Oct 26, 1:25 am, Elizabeth wrote: I need to figure out how to assign a code in a separate cell to a range of time. Here's part of the codes I need to use (the last column being the code for any times within that range of time: 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 BUT, I need to be able to allow for multiple answers in one cell. Here's an example of what I need it to do: 12:00 AM 8:55 AM 1 8:55 AM 9:05 AM 1, 2 9:05 AM 9:55 AM 2 9:55 AM 10:05 AM 2 10:05 AM 10:25 AM 2 10:25 AM 12:00 PM 2, 3 12:00 PM 12:30 PM 3 This is for a research project that has do be done by Monday, can someone help? I'm mildly computer literate but would need mostly laymans language. HELP! -- Elizabeth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Actually, I have codes for every time of the day and night. I just didn't
want send a gigantic message! Is that something that is needed to answer the question? I tried to figure out an IF formula but just can't get it to work. Thank you, I look forward to having help! Elizabeth -- Elizabeth "Pete_UK" wrote: Presumably, you won't have any times before 5:00 am or after 7:00pm? This is a nice little problem, but it's a bit late here in the UK so I haven't got time to delve into it. If you haven't had any solutions by the morning, I'll look at it then. I think you just need a simple table of times and codes, and an IF to give you the composite codes. Pete On Oct 26, 1:25 am, Elizabeth wrote: I need to figure out how to assign a code in a separate cell to a range of time. Here's part of the codes I need to use (the last column being the code for any times within that range of time: 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 BUT, I need to be able to allow for multiple answers in one cell. Here's an example of what I need it to do: 12:00 AM 8:55 AM 1 8:55 AM 9:05 AM 1, 2 9:05 AM 9:55 AM 2 9:55 AM 10:05 AM 2 10:05 AM 10:25 AM 2 10:25 AM 12:00 PM 2, 3 12:00 PM 12:30 PM 3 This is for a research project that has do be done by Monday, can someone help? I'm mildly computer literate but would need mostly laymans language. HELP! -- Elizabeth |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth "Elizabeth" wrote: I need to figure out how to assign a code in a separate cell to a range of time. Here's part of the codes I need to use (the last column being the code for any times within that range of time: 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 BUT, I need to be able to allow for multiple answers in one cell. Here's an example of what I need it to do: 12:00 AM 8:55 AM 1 8:55 AM 9:05 AM 1, 2 9:05 AM 9:55 AM 2 9:55 AM 10:05 AM 2 10:05 AM 10:25 AM 2 10:25 AM 12:00 PM 2, 3 12:00 PM 12:30 PM 3 This is for a research project that has do be done by Monday, can someone help? I'm mildly computer literate but would need mostly laymans language. HELP! -- Elizabeth |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
I was hoping you would give a bit more detail, rather than just tell
me that there is more detail. I can only work with the example data you have given, so you may have to adapt what follows to suit your particular circumstances. First of all, you need to set up a small table somewhere, made up of the start times of your ranges and the codes that go with them. Suppose you put this in X1:Y7 of your sheet, and it looks like this: 0:00 AM 0 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 Assume also that your starting times are in column A with finish times in column B, and that the data starts on row 2. You will need a formula like the following: =VLOOKUP(A2,X$1:Y$7,2)&IF(VLOOKUP(B2,X$1:Y$7,2)<V LOOKUP(A2,X$1:Y $7,2),", "&VLOOKUP(B2,X$1:Y$7,2),"") (all one formula - be wary of spurious line breaks in the newsgroups). Copy the formula down for as many rows as you need. Hope this helps. Pete On Oct 26, 3:11 am, Elizabeth wrote: Actually, I have codes for every time of the day and night. I just didn't want send a gigantic message! Is that something that is needed to answer the question? I tried to figure out an IF formula but just can't get it to work. Thank you, I look forward to having help! Elizabeth -- Elizabeth "Pete_UK" wrote: Presumably, you won't have any times before 5:00 am or after 7:00pm? This is a nice little problem, but it's a bit late here in the UK so I haven't got time to delve into it. If you haven't had any solutions by the morning, I'll look at it then. I think you just need a simple table of times and codes, and an IF to give you the composite codes. Pete On Oct 26, 1:25 am, Elizabeth wrote: I need to figure out how to assign a code in a separate cell to a range of time. Here's part of the codes I need to use (the last column being the code for any times within that range of time: 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 BUT, I need to be able to allow for multiple answers in one cell. Here's an example of what I need it to do: 12:00 AM 8:55 AM 1 8:55 AM 9:05 AM 1, 2 9:05 AM 9:55 AM 2 9:55 AM 10:05 AM 2 10:05 AM 10:25 AM 2 10:25 AM 12:00 PM 2, 3 12:00 PM 12:30 PM 3 This is for a research project that has do be done by Monday, can someone help? I'm mildly computer literate but would need mostly laymans language. HELP! -- Elizabeth- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Assume your numbers are in column E, starting on row 2. Put this
formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Yes, the second answer is great, thanks!
First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Did you see my earlier response? With this extra detail, you will need
to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Ok great! Thank you for your help! If you look back to my original post
you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
You're welcome, Elizabeth, but you original example only showed two
adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Thank you so very much!!! I'll be sure not to use an example next time and
include the whole table. I've tried it and couldn't make it work. Do I paste it into the cell, changing the cell id's as appropriate in the formula, or do I need to perform a function using a diff. method? I am not that savvy on this, as you can tell, so I am deeply deeply thankful!!!! E -- Elizabeth "Pete_UK" wrote: You're welcome, Elizabeth, but you original example only showed two adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Send your file to me:
pashurst <at auditel.net Change the obvious. I'll send you back a working version. Pete On Oct 27, 3:24 am, Elizabeth wrote: Thank you so very much!!! I'll be sure not to use an example next time and include the whole table. I've tried it and couldn't make it work. Do I paste it into the cell, changing the cell id's as appropriate in the formula, or do I need to perform a function using a diff. method? I am not that savvy on this, as you can tell, so I am deeply deeply thankful!!!! E -- Elizabeth "Pete_UK" wrote: You're welcome, Elizabeth, but you original example only showed two adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
I sent the file. THANK YOU!
-- Elizabeth "Pete_UK" wrote: Send your file to me: pashurst <at auditel.net Change the obvious. I'll send you back a working version. Pete On Oct 27, 3:24 am, Elizabeth wrote: Thank you so very much!!! I'll be sure not to use an example next time and include the whole table. I've tried it and couldn't make it work. Do I paste it into the cell, changing the cell id's as appropriate in the formula, or do I need to perform a function using a diff. method? I am not that savvy on this, as you can tell, so I am deeply deeply thankful!!!! E -- Elizabeth "Pete_UK" wrote: You're welcome, Elizabeth, but you original example only showed two adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
Got it, amended it, and sent it back to you.
Pete On Oct 27, 8:51 pm, Elizabeth wrote: I sent the file. THANK YOU! -- Elizabeth "Pete_UK" wrote: Send your file to me: pashurst <at auditel.net Change the obvious. I'll send you back a working version. Pete On Oct 27, 3:24 am, Elizabeth wrote: Thank you so very much!!! I'll be sure not to use an example next time and include the whole table. I've tried it and couldn't make it work. Do I paste it into the cell, changing the cell id's as appropriate in the formula, or do I need to perform a function using a diff. method? I am not that savvy on this, as you can tell, so I am deeply deeply thankful!!!! E -- Elizabeth "Pete_UK" wrote: You're welcome, Elizabeth, but you original example only showed two adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
You are TRULY amazing. You saved me! Thank you so very very very very much!!!
E -- Elizabeth "Pete_UK" wrote: Got it, amended it, and sent it back to you. Pete On Oct 27, 8:51 pm, Elizabeth wrote: I sent the file. THANK YOU! -- Elizabeth "Pete_UK" wrote: Send your file to me: pashurst <at auditel.net Change the obvious. I'll send you back a working version. Pete On Oct 27, 3:24 am, Elizabeth wrote: Thank you so very much!!! I'll be sure not to use an example next time and include the whole table. I've tried it and couldn't make it work. Do I paste it into the cell, changing the cell id's as appropriate in the formula, or do I need to perform a function using a diff. method? I am not that savvy on this, as you can tell, so I am deeply deeply thankful!!!! E -- Elizabeth "Pete_UK" wrote: You're welcome, Elizabeth, but you original example only showed two adjacent time periods, so I thought you meant you only wanted the first and the last period to be returned from the formula. If, indeed, you need things like: "10, 1, 2, 3" or "3, 4, 5, 6, 7" or even "7, 8, 9, 10, 1, 2, 3, 4, 5, 6" to be returned, covering each time period within the span, then the formula at present will not do this. You could do this by having another table which would list all possible outcomes from the current formula and in the next column what you would like each one to be shown as and then use another VLOOKUP formula to do this "translation". Suppose you started in cell Z1 - you would first of all list the simplest output from the current formula, i.e.: 1 2 3 4 5 6 7 8 9 10 and then follow with the next simplest (adjacent pairs), i.e.: 1, 2 2, 3 3, 4 4, 5 5, 6 and so on up to: 9, 10 10, 1 Following on from this in the same column you could have values which are two codes apart: 1, 3 2, 4 3, 5 4, 6 and then three codes apart: 1, 4 2, 5 3, 6 and so on. The order is not important, but by adopting a systematic approach like this you could ensure that you cover them all. Then in column AA you could list what you want these to be transformed into, so in the middle of the table you would have something like this: 1, 4 1, 2, 3, 4 2, 5 2, 3, 4, 5 3, 6 3, 4, 5, 6 and do this all the way down to complete the table. Your new formula would be something like this: =VLOOKUP(C2,Z:AA,2,0) where C2 is the cell with the current formula. If you copy this down then you will get the output you require. If you want to, you could combine both formulae into one, so that you would end up with: =VLOOKUP(VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1: Y$11,2)<VLOOKUP(A2,X $1:Y$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),""),Z:AA,2,0) and this will give you the required output directly when copied down. If you think this is too much trouble, and you don't have a lot of data that spans multiple time periods, then you can always make the necessary changes to the output manually. Hope this helps, and hope you get your project in on time. Pete On Oct 26, 8:55 pm, Elizabeth wrote: Ok great! Thank you for your help! If you look back to my original post you'll see that, yes, I need to be able to have the range of codes. See first post for example. Thank you, I will try this and hope it works. Even that would be a great help! I sent you the rest of the table because you said you were hoping I would, thank you for looking at it again!...I feel like we are missing part of each other's communique. : ) Thank you SO much! E -- Elizabeth "Pete_UK" wrote: Did you see my earlier response? With this extra detail, you will need to amend the table slightly, as follows: 0:00 AM 9 1:00 AM 10 5:01 AM 1 9:01 AM 2 11:01 AM 3 1:01 PM 4 3:01 PM 5 5:01 PM 6 7:01 PM 7 9:01 PM 8 11:01 PM 9 so your table would now occupy X1:Y11, and the formula I gave you earlier would become: =VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y $11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"") This covers the examples you gave in your first post, but are you now saying that if the start time was 8:55 am and finish time was 11:15 am then you would like to see 1, 2, 3 returned from the formula? Pete On Oct 26, 5:00 pm, Elizabeth wrote: Yes, the second answer is great, thanks! First one: that why I asked. : ) Here is the full detail, I'd need, as the example in my first msg, to be able to have a second code auto-populate in the appropriate column as well. So if two time span categories overlap there could be up to 3 or 4 codes. Thank you so much!!!! E 5:01 AM 9:00 AM 1 9:01 AM 11:00 AM 2 11:01 AM 1:00 PM 3 1:01 PM 3:00 PM 4 3:01 PM 5:00 PM 5 5:01 PM 7:00 PM 6 7:01 PM 9:00 PM 7 9:01 PM 11:00 PM 8 11:01 PM 1:00 AM 9 1:01 AM 5:00 AM 10 -- Elizabeth "Pete_UK" wrote: Assume your numbers are in column E, starting on row 2. Put this formula in F2: =IF(E2=2,99,E2) and copy down. This just replaces 2 in column E with 99 - other values remain the same. Is this what you want? Hope this helps. Pete On Oct 26, 4:45 am, Elizabeth wrote: I also have a simpler issue but still can't make the darn thing work. I need to create a formula so that if there is a certain number, such as a 2 in one column, it automatically enters a diff number, in this case 99, in the next column. I'm trying to use the IF function but I just can't figure the darn thing out. Thanks Again!!! E -- Elizabeth- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign a code to a range of time
You're welcome, Elizabeth - thanks for feeding back.
Pete On Oct 28, 4:59 pm, Elizabeth wrote: You are TRULY amazing. You saved me! Thank you so very very very very much!!! E -- Elizabeth "Pete_UK" wrote: Got it, amended it, and sent it back to you. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
I want to assign a value to the letter R in a cell range | Excel Worksheet Functions | |||
Cannot assign a range to seriecollection values | Charts and Charting in Excel | |||
assign a value to a range | Excel Discussion (Misc queries) | |||
assign value for period of time | Excel Discussion (Misc queries) |