Excel Compare Columns and Increments

[dygitaldude]

Please help!

This is my first time posting. Please excuse me, if I'm not clear enough. I'm trying to figure out a way to number the rows as in Col C below. The first instance of Col A should be numbered 1, if subsequent value is same as Col B then 1 or increment 1.

Thanks



[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]Bat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]Ball[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]Glove[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD]Bat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]200[/TD]
[TD]Bat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200[/TD]
[TD]Ball[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]200[/TD]
[TD]Ball[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]200[/TD]
[TD]Glove[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]200[/TD]
[TD]Glove[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]300[/TD]
[TD]Ball[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]300[/TD]
[TD]Glove[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

In cell C1, set the value 1.
In cell C2 put the following formula and the copies down

=IF(A1=A2,SI(B1<>B2,C1+1,C1),1)

 

[DRSteele]

If you have up-to-date Excel365 Insider, this formula will work. It uses the new Excel functions UNIQUE and FILTER.

C1:

[FONT=Verdana]=MATCH(A1&B1,UNIQUE(FILTER($A$1:$A$11&$B$1:$B$11,$A$1:$A$11=A1)),0)[/FONT]

 

[dygitaldude]

In cell C1, set the value 1.
In cell C2 put the following formula and the copies down

=IF(A1=A2,SI(B1<>B2,C1+1,C1),1)

Hello Dante,

The formula did not work unless I did something incorrect

[TABLE="width: 192"]
<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] A
[/TD]
[TD="class: xl63, width: 64"]B
[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100
[/TD]
[TD="class: xl63, width: 64"]Bat[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100
[/TD]
[TD="class: xl63, width: 64"]Ball
[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100
[/TD]
[TD="class: xl63, width: 64"]Glove
[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]bat
[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]bat
[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]ball[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dygitaldude]

Hello DR,

I do not have the excel365 Insider.

 

[DanteAmor]

Hello Dante,

The formula did not work unless I did something incorrect

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"] A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100[/TD]
[TD="class: xl63, width: 64"]Bat[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100[/TD]
[TD="class: xl63, width: 64"]Ball[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]100[/TD]
[TD="class: xl63, width: 64"]Glove[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]bat[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]bat[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]200[/TD]
[TD="class: xl63, width: 64"]ball[/TD]
[TD="class: xl63, width: 64, align: center"]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]

Sorry, this is the correct formula:

=IF(A1=A2,IF(B1<>B2,C1+1,C1),1)

Try again

 

[dygitaldude]

Sorry, this is the correct formula:

=IF(A1=A2,IF(B1<>B2,C1+1,C1),1)

Try again

You sir are a genius. It worked like a charm!!!

Thank you, god bless.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.