duplicates formula

Christine Menke · 08/27/21

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 · 09/01/21

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 · 12/09/21

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 · 08/27/21

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 · 08/30/21

Hi

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

Paul Newcome · 08/30/21

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 · 08/31/21

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 · 08/31/21

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 · 09/01/21

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 · 09/01/21

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 · 09/02/21

Thanks a lot! Now it works :-)

Paul Newcome · 09/02/21

Happy to help. 👍️

Christine Menke · 12/09/21

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 · 12/09/21

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 · 12/10/21

Thanks a lot @Paul Newcome ,

it works!!!! 😃

Paul Newcome · 12/10/21

Happy to help. 👍️

Ankie · 05/18/22

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. · 05/19/22

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

duplicates formula

Best Answers

Answers

Help Article Resources

Categories