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
Best Answers
-
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.
-
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))
Answers
-
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?
-
Hi
yes always 2 letters in the front following by 4 numbers at the end.
-
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.
-
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
-
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)
-
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
-
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.
-
Thanks a lot! Now it works :-)
-
Happy to help. 👍️
-
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
-
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))
-
Thanks a lot @Paul Newcome ,
it works!!!! 😃
-
Happy to help. 👍️
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!