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

	A	B	C
1	100	Bat	1
2	100	Ball	2
3	100	Glove	3
4	200	Bat	1
5	200	Bat	1
6	200	Ball	2
7	200	Ball	2
8	200	Glove	3
9	200	Glove	3
10	300	Ball	1
11	300	Glove	2

<tbody>
</tbody>

 

[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

A	B	C
100	Bat	1
100	Ball	#NAME?
100	Glove	#NAME?
200	bat	1
200	bat	#NAME?
200	ball	#NAME?

<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>

 

[dygitaldude]

Hello DR,

I do not have the excel365 Insider.

 

[DanteAmor]

Hello Dante,

The formula did not work unless I did something incorrect

A	B	C
100	Bat	1
100	Ball	#NAME?
100	Glove	#NAME?
200	bat	1
200	bat	#NAME?
200	ball	#NAME?

<tbody>
</tbody>

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.