Sequence

[BingoGeorge]

Hi Folks, I am horrible with my "if" statements. I hope I can get some help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]SEQ[/TD]
[TD]ID[/TD]
[TD]ETc[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]2000[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[TD]2000[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]2001[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Onions[/TD]
[TD]2002[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]steak[/TD]
[TD]2002[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need does it the SEQ numbers to do +1 if the ID shows 1 OR repeat the same SEQ is the number shows 2.

Let me know what you think.
Thanks

 

[Joe4]

Try this in cell C2 (ID column) and copy down:

=IF(B2=B1,C1+1,1)

Or if your numbers aren't always in order, use this formula instead:

=COUNTIF(B$2:B2,B2)

 

[63falcondude]

With 2000 in B2, try this in B3

=IF(C3=1,B2+1,B3)

Copied down.

 

[BingoGeorge]

With 2000 in B2, try this in B3

=IF(C3=1,B2+1,B3)

Copied down.

When I try that is says in a error box "Too many circulars references"
When I copy down they are all "0".



so B2 being the start point at 2000. the ID is 1
B3 should also be 2000 since the ID is 2.
B4 should be 2001 since the id is 1.

These numbers vary, but the ID will always be either 1 or 2.

since I keep adding to the list, its possible that the order changes since I might filter another column.

 

[Joe4]

Did you try either of the two solutions I provided?

 

[BingoGeorge]

Did you try either of the two solutions I provided?

The first one does not work since B1 is a column header "SEQ", but I modified it to a row down, and still no luck.

=IF(C3=1,B2+1,B3)
give me a much of zeros

 

[63falcondude]

Sorry, typo...

Should have been

B3 =IF(C3=1,B2+1,B2)

 

[Joe4]

The first one does not work since B1 is a column header "SEQ", but I modified it to a row down, and still no luck.

It doesn't matter. If your have a title row in row 1, and your data is in columns A, B, and C, then it works. I tested both of my replies and verified it.

My guess is that your structure is different, and you are trying to adjust something and not doing it properly.
If you cannot work, try describing EXACTLY how your sheet is structured. What columns are you using? What row is your first row of actual data in?

=IF(C3=1,B2+1,B3)
give me a much of zeros

That is not either one of my formulas.

 

[BingoGeorge]

Sorry, typo...

Should have been

B3 =IF(C3=1,B2+1,B2)

AH_HA!!!! That works!
Thank you

 

[Joe4]

OK, I see. I misunderstood your question.
I thought you were trying to populate column C from column B, not column B from column C.
Sorry about that.

Looks like falcondude was able to help you out.