Formula assistance please . .


J

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.
 
Ad

Advertisements

M

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

J.Scargill

Fabulous Mike, thankyou very much.

Mike H said:
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.
 
Ad

Advertisements

M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top