Formula assistance please . .

[J.Scargill]

Hi people,
Had a query passed to me about a formula and despite my efforts, have
reached the decision to seek help from your good selves!
Worksheet looks like this;

Col F Col AB Col AC
8564848485858565 5236523652365236 0
8484848484848484 7412741274127412 0
1234567891234567 8484848484848484 1
2582582582582586 8484848456789253 0
8484848484856547 8567458698562311 0

Column F is an ever-increasing year-to-date list of 16 digit codes.
Column AB is the latest weeks list of codes that our clerk pastes in.
Column AC is our duplicate checker; current formula is
=IF(AB14="","0",COUNTIF(F:F,AB14))

I am now wanting to slightly amend the formula so that it checks against the
last 12 digits only of the numbers in Col F.
Hope you can help.
Thanks.

 

[Mike H]

Hi,

Try this

=SUMPRODUCT(--(RIGHT($F$1:$F$6,12)=RIGHT(AB3,12)))

Note you can only use full columns for the range in Excel 2007
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[J.Scargill]

Fabulous Mike, thankyou very much.

Hi,

Try this

=SUMPRODUCT(--(RIGHT($F$1:$F$6,12)=RIGHT(AB3,12)))

Note you can only use full columns for the range in Excel 2007
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

your welcome
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.