Formular for refrencing two referances

[steve clarke]

Hi, If anyone can help me out that would be great.

I don't know if this is possiable, or is something that needs to be
done in VBA rather than straight in excel.

I have two referances that i need to cross check to produce a new
referance. there are duplicates of both original refrences, (see the
table below). I need the output of the formular to count up from 1
where the ref1 is the same, however it must only increase where ref2
changes. When Ref1 changes the output must start increasing from 1
again.

The following table shows the two refrences i already have (ref1 & 2)
as well as the required Output from the formula.

Ref1 Ref2 Output
200769 FI61411 1
200769 FI61411 1
200769 FI61411 1
200769 FI61507 2
201225 B323031 1
201225 FI33006 2
201225 FI33006 2
201225 FI33009 3
201225 FI33009 3
201225 FI33009 3
201397 HS22300 1
201476 EN50669 1

I have tried a number of count/if methods, though I just cant seem to
come up with a solution to this problem.

 

[Teethless mama]

Assuming your data in A1:B13, header row in row 1
C1: leave it blank

In C2: =IF(AND(A2=A1,B2=B1),C1,IF(AND(A2=A1,B2<>B1),C1+1,1))

coppy down

 

[Don Guillett]

Maybe you can just select col A:B ranges>data>filter>advanced filter>filter
in place>unique

 

[steve clarke]

Brilliant,

Thanks for your assistance!