duplicates formula

Hi Experts:

I would like to identify the duplicated in column Model# and than mark the once which have the lowest article#. The one marked in red are the once which should be marked in column model count.

It's getting complicate as the article# is build out of letter and numbers, it's build in alphabetical order.

Do you have an idea for a formula.

I started with this, but not sure how to check the article number.

=IF(AND(COUNTIFS([Model#]:[Model#], [Model#]@row, [article#]:[article#], <>"") > 0, [Article#]:[Article#] > [Article#]@row ?????????????), true, false)

Thanks


Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That gets a little bit trickier. First we need to parse out the Article# so that each letter can be swapped out based on a reference table then put it all back together into a number string. From there we can return back to the MIN/COLLECT mentioned above.

    Since it will always be six digits for the Article# and the last 4 are already a number string, we should be able to get away with just using a different formula in the existing Helper Column and maintaining the MIN/COLLECT formula.

    The table would have all of the letters in the alphabet in one column and their replacement numbers in another column. I suggest starting with 10 for "A" so that every letter is replaced by a 2 digit number instead of some being one digit and others being 2 (consistency where possible usually helps make solutions a bit easier to put together).

    Letter.....Number

    A..............10

    B..............11

    C..............12

    so on and so forth


    Now in the helper column we have:

    =VALUE(INDEX(Number:Number, MATCH(LEFT([Article#]@row), Letter:Letter, 0)) + "" + INDEX(Number:Number, MATCH(MID([Article#]@row, 2, 1), Letter:Letter, 0)) + "" + RIGHT([Article#]@row, 4))


    From there the MIN/COLLECT already in place should be functioning properly.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Lets give this one a try...

    =VALUE(INDEX(Number:Number, MATCH(LEFT([Article#]@row), Letter:Letter, 0)) + "" + IFERROR(INDEX(Number:Number, MATCH(MID([Article#]@row, 2, 1), Letter:Letter, 0)), MID([Article#]@row, 2, 1)) + "" + RIGHT([Article#]@row, 4))

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!