If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula assistance please . .
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. |
#2
|
|||
|
|||
Formula assistance please . .
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" wrote: 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. |
#3
|
|||
|
|||
Formula assistance please . .
Fabulous Mike, thankyou very much.
"Mike H" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Formula assistance please . .
your welcome
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "J.Scargill" wrote: Fabulous Mike, thankyou very much. "Mike H" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|