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

ppenalba

New Member
Joined
Jul 10, 2013
Messages
4
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

1000001
2000002
3000003
1000011
1000021
1000031
1000041
2000012
1000051
1000061
3000033
2000022

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

Some videos you may like

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
Joined
Jul 9, 2013
Messages
495
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
Joined
Jul 10, 2013
Messages
4
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
Joined
Jul 9, 2013
Messages
495
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
Joined
Jul 10, 2013
Messages
4
Thanks though, but yes i am trying to find a way to do it through excel rather than vba.
 

Profex13

New Member
Joined
Jul 10, 2013
Messages
1
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

Active Member
Joined
Jul 9, 2013
Messages
495
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
Joined
Jul 9, 2013
Messages
495
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
Joined
Jul 10, 2013
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,780
Messages
5,470,723
Members
406,719
Latest member
ensbana

This Week's Hot Topics

Top