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 -