Excel 2010: How to have different sequences in a single column.

[ppenalba]

Hi i am trying to find a way to get different sequences on a single column according to the value on another column to the right of it. for example every time the value of the right column is "1" it adds to a specific sequence, every tiem its "2" it adds to another sequence. how could this be done? Here is an example of what i would want the code to do. I am looking o see if this can be done with excel formulas rather than VBA please if it is possible. Thanks

[TABLE="width: 267"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]100000[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]200000[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]300000[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]100001[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100002[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100003[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100004[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]200001[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]100005[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]100006[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]300003[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]200002[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Lopez]

Without using VBA, you are going to have use Nest IFs, so something along the lines of:

=IF(B2=1,1,IF(B2=2,2,IF(B2=3,3,"NOT FOUND")))

Make sense?

 

[ppenalba]

Without using VBA, you are going to have use Nest IFs, so something along the lines of:

=IF(B2=1,1,IF(B2=2,2,IF(B2=3,3,"NOT FOUND")))

Make sense?

This would only give me the numbers "1" "2" or "3" wouldnt it? i want it to have a sort of sequence for each. Each time it reads one i want it to add a number to that specific sequence and display it beside that number.

Do i explain myself?

 

[Jeffrey Lopez]

I do apologize, I did not understand your sequence question, but not sure how that can be done without the use of VBA someone else might be able to shed some light on it.

 

[ppenalba]

Thanks though, but yes i am trying to find a way to do it through excel rather than vba.

 

[Profex13]

Assuming the numbers start in cell B1 you can use the following formula:

=B1*100000+COUNTIF(B$1:B1,B1)-1

It will count how many times the value has appeared in the list above the current cell. The key is to lock the starting cell (B$1) and let the end range change.

 

[Jeffrey Lopez]

No problem, the issue you are going to run into is trying to keep a running total of what the sequence is as it goes down the list, where as with VBA, you can use Counters and Arrays to keep track of all that, I am interested to see what someone else might come up with!

 

[Jeffrey Lopez]

Assuming the numbers start in cell B1 you can use the following formula:

=B1*100000+COUNTIF(B$1:B1,B1)-1

It will count how many times the value has appeared in the list above the current cell. The key is to lock the starting cell (B$1) and let the end range change.

Thats great! Didn't think about use'n a CountIF to add the totals...

 

[ppenalba]

Assuming the numbers start in cell B1 you can use the following formula:

=B1*100000+COUNTIF(B$1:B1,B1)-1

It will count how many times the value has appeared in the list above the current cell. The key is to lock the starting cell (B$1) and let the end range change.

Thank you very much! this was what i wanted.