duplicates formula

Christine Menke

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

Paul Newcome

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

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

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?

Christine Menke

Hi

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

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.

Christine Menke

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

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)

Christine Menke

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

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.

Christine Menke

Thanks a lot! Now it works :-)

Paul Newcome

Happy to help. 👍️

Christine Menke

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

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

Christine Menke

Thanks a lot @Paul Newcome ,

it works!!!! 😃

Paul Newcome

Happy to help. 👍️

Ankie

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.

https://community.smartsheet.com/discussion/comment/330170#Comment_330170

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