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

#### ppenalba

##### New Member
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

 100000 1 200000 2 300000 3 100001 1 100002 1 100003 1 100004 1 200001 2 100005 1 100006 1 300003 3 200002 2

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Jeffrey Lopez

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

Code:
``=IF(B2=1,1,IF(B2=2,2,IF(B2=3,3,"NOT FOUND")))``
Make sense?

#### ppenalba

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

Code:
``=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

##### Active Member
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

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

#### Profex13

##### New Member
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

#### Jeffrey Lopez

##### Active Member
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

##### Active Member
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

##### New Member
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.