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.

    thinkspi.com

  • 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))

    thinkspi.com

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do your article numbers always have two letters at the beginning or can it be variable? Will the numbers always be the last four digits, or could there be 3 or 5 or some other number of digits?

    thinkspi.com

  • Hi

    yes always 2 letters in the front following by 4 numbers at the end.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. We can grab the numbers on the end using

    VALUE(RIGHT([Article#]@row, 4))


    Where I get lost though is comparing what you say to your screenshot. You mentioned flagging the lowest article number that is a duplicate. I assume by "duplicate" you mean those first two letters? If so, that makes sense for "GW3895" and "HQ9885", but I am unsure based on your logic why the other two would be flagged.

    thinkspi.com

  • Hi @Paul Newcome ;

    sorry I did a mistake in my screen shot, you are right.

    If they have the same Model# I would like to find out the lowest value in the article# column.

    e.g. model# LSD62 -> article# HP5275


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am going to suggest a helper column where you use something like this to pull the number:

    =VALUE(RIGHT([Article#]@row, 4))


    Then you can use this to flag the row with the lowest value based on Model #:

    =IF([Helper Column]@row = MIN(COLLECT([Helper Column]:[Helper Column], [Model#]:[Model#], @cell = [Model#]@row)), 1)

    thinkspi.com

  • Hi @Paul Newcome ,

    perfect it works for 95% of the articles. But in case I have a step in the alphabetical part of the article# it counts wrong. On below example the red marked art is the lowest one as GV comes before GW. So I guess I need another helper to check first the alphabetical order and the the numbers. Any idea? Thanks


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

    thinkspi.com

  • Thanks a lot! Now it works :-)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • Hi @Paul Newcome

    as you helped me before on this, I face a new challenge.

    The above article # include now also numbers on the 2nd position.

    Here some examples: H03612 or G58523

    Any idea how adjust above formula that I'm able to count model nr.

    Thanks

  • 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))

    thinkspi.com

  • Thanks a lot @Paul Newcome ,

    it works!!!! 😃

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • Hi There,

    I'm trying to create a formula for duplicates in my smartsheet, but so far all the example I found online give me #UNPARSEABLE.

    The duplicate column is a checkbox and changing it to text does not make a difference. Can you please advise? thanks

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ankie

    You'll want to type the @row portion of the formula in lower case for the formula to identify it.

    Try:

    =IF(COUNTIF(MMR:MMR, MMR@row) > 1, 1)

    Cheers,

    Genevieve