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.

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


"steve clarke" wrote:

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.

Maybe you can just select col A:B rangesdatafilteradvanced filterfilter
in placeunique

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"steve clarke" wrote in message
...
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.

Brilliant,

Thanks for your assistance!