New Sequence

[Cantonalives]

All,
Any help appreciated.

I have a table based on MoSCoW ratings. So criteria determines place in sequence. However, in another column I need to ability to be able to type in a value and have it reflected in Column2. But unique only.

In D I have placed the number 2 and would like that to become number 2 and then current number 2 to become 3 etc. And this could happen with numerous additions.

See attached which may make my gibberish clearer. Thanks...


-CL

 

[Cantonalives]

If anyone gets a moment pls? Assuming its possible? Thanks for any guidance..

-CL

 

[vletm]

MoSCoW ratings ... is that something which someone should know?
So criteria determines place in sequence. ... hmm?
in another column I need to ability to be able to type in a value and have it reflected in Column2. But unique only. ... so? column I is empty ...
In D I have placed the number 2 and would like that to become number 2 and then current number 2 to become 3 etc. And this could happen with numerous additions. ... If You write 2 then it'll come 2 and so on ... what is Your point?

Something is missing as ...
What would be connection Your texts and term 'New Sequence'?
What are You looking for?

 

[Cantonalives]

Apologies - difficult one the explain.

So, Unique numbers only in ascending order. If a 2 is typed in Column D, it becomes number 2 and the sequence and therefore the item currently number 2 would move to number 3 and so on.


Therefore criteria determine the number on the list as per first column, except when items are populated in column 2.

Only way I could get close was to assume there is another column where If 'D' is blank then A, else D. Issue is that I would then end up with duplicates.

Hope that makes more sense?

Thanks anyway,


-CL

 

[vletm]

Hope that makes more sense?

hmm ... hmm ... No!

 

[Cantonalives]

Will try once more - please see attached. Column A shows a priority order. However, someone could chose to enter a number in Column C which would then become that typed number. Meaning that I would then get a duplicate number. In Column E I then have 2 number 2s. So, I would like the item formerly at Number 2, to then move to number 3. So only have unique numbers. Column A would be Priority, however this can be overridden by column C and something being forced to a priority number.

-CL

 

[bosco_yip]

Just guessing...........

1] I put formula result in Column G

In G5, formula copied down :

=IF(D5<>"",D5,IF((A5>=INDEX($D$5:$D$37,MATCH(1,INDEX(--($D$5:$D$37<>""),0),0)))*(A5<=INDEX($B$5:$B$37,MATCH(1,INDEX(--($D$5:$D$37<>""),0),0))),A5+1,A5))

2] See attached file

Regards
Bosco

 

[vletm]

Cantonalives
Use this ...

 

[Cantonalives]

Just guessing...........

1] I put formula result in Column G

In G5, formula copied down :

=IF(D5<>"",D5,IF((A5>=INDEX($D$5:$D$37,MATCH(1,INDEX(--($D$5:$D$37<>""),0),0)))*(A5<=INDEX($B$5:$B$37,MATCH(1,INDEX(--($D$5:$D$37<>""),0),0))),A5+1,A5))

2] See attached file

Regards
Bosco

Thanks for this. Works great until you have 2 numbers in coumn D. Then it duplicates. For instance, we had number 2 but then adding say 4 to another item, duplicates the number 4.

 

[Cantonalives]

Cantonalives
Use this ...

Hi vletm,
Firstly - thank you for bearing with me! And secondly - thank you! Awesome. Works a treat.

Much appreciated.

-CL

 

[Peter Bartholomew]

Thought the problem looked interesting so I had a go using named formulas for intermediate results (an array of ranks available after forcing and a list of items that still require ranks)

 

[Cantonalives]

Thank you Peter - that is a brilliant solution!!!

-CL