Multiple Condition Duplicate Line

Hello,

I am creating a tracking system for PO's that need to be entered into a system. The problem is that a PO can be split into multiple truckloads and delivered to the facility on mutliple dates. The facility enters each PO that is needed based on a schedule but if the PO had been received previously, they do not want to waste time searching for said PO if it had been entered previously.

With that being said, I am trying to flag all duplicate PO's in the tracking spreadsheet below based on multiple criteria.

I want to insert a column into the report that gives me back "PO Received" if the PO # is a duplicate and the duplicated lines have an attachment but the PO Entered checkbox is not checked, OR have it say "PO Entered" if the PO # is a duplicate and the duplicated lines have the PO Entered checkbox checked. If it is only a duplictae and the other two conditions are not met, it should say "PO Needed".

Any help on this is appreciated!

Find more posts tagged with

IF(OR

IF(AND

duplicates

Accepted answers

TVang

@Ryan Walsh

Create a helper column to store a value when an attachment is added to the row: ATTACHMENT.

Create an automated workflow that triggers when a row is changed or added AND an attachment is added. Set the action to store "1" in ATTACHMENT.

Set your PO STATUS column formula as follows:

IF(COUNTIFS([PO #]:[PO #], [PO #]@row, [PO Entered]:[PO Entered], 1) > 0
   ,"PO Entered"
   ,IF(COUNTIFS([PO #]:[PO #], [PO #]@row, ATTACHMENT:ATTACHMENT, 1, [PO Entered]:[PO Entered], 0) > 0
      ,"PO Received"
      ,"PO Needed"))

All comments

Jgorsich

First, follow this post to add a column called "Row" that just has the row number in a manner you can take advantage of in formulas:

Is there a way to reference the row above? - Page 2 — Smartsheet Community

Then, using that row column, you'd need an if() statement that meets your criteria (which I was about to rewrite, but you actually wrote it quite clearly). That might look like this (I don't know off hand how to check for an attachment and don't have time to check right now - figure that out by searching past replies and sub it in in the "XXX" fields): (combine all the following into a single formula, I've split it up for readability)

=if(count(collect([PO#]:[PO#],[PO#]@row,[row]:[row],>row@row,XXXXX,XXXXX,[PO Entered]:[PO entered],0))>0,"PO Received",

if(count(collect([PO#]:[PO#],[PO#]@row ,[row]:[row],>row@row, [PO Entered]:[PO entered],1))>0,"PO Entered",

if(count(collect([PO#]:[PO#],[PO#]@row ,[row]:[row],>row@row , "PO Needed",

"")))

the first line checks for POs that have a PO entry above this row, have an attachment (you've got to figure out how to do that and replace the XXX entries), and DON'T have the PO entered column checked.

The second line says "if that condition is false, then" it checks for a PO that has the same number above the row you are on but has the PO entered column checked

The third line says "if both of those are false, then" if you've got the same PO number above, say "PO needed".

Finally the fourth line makes your formula look blank if NONE of the conditions are met.

good luck.

TVang

@Ryan Walsh

Create a helper column to store a value when an attachment is added to the row: ATTACHMENT.

Create an automated workflow that triggers when a row is changed or added AND an attachment is added. Set the action to store "1" in ATTACHMENT.

Set your PO STATUS column formula as follows:

IF(COUNTIFS([PO #]:[PO #], [PO #]@row, [PO Entered]:[PO Entered], 1) > 0
   ,"PO Entered"
   ,IF(COUNTIFS([PO #]:[PO #], [PO #]@row, ATTACHMENT:ATTACHMENT, 1, [PO Entered]:[PO Entered], 0) > 0
      ,"PO Received"
      ,"PO Needed"))

Ryan Walsh

@TVang - This worked perfectly, thanks so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.