Identifying duplicates between 2 multiselect Dropdowns

Richard Castle

Hi there. Really hoping you can help as I am a little stuck here. We are using a smartsheet to log and plan change requests across a wide variety of applications. A single change will only ever be a single row with it's own start and finish dates. A single change may affect multiple applications and some of these applications may need to be "locked" from any other changes being made at the same time. These locked conflicts need to be identified and highlighted.

An example of this is below (from Excel). The "Impacted Applications" and "Locked Applications" columns are multi-select dropdowns in Smartsheets.

Change 1 will involve ERP1, 2 and 3 and during it's timeline (Jan 21), ERP3 cannot be impacted by any other changes at the same time. Change 3 has therefore been highlighted as a conflict as it is due to take place during the same time. It states that ERP3 is the conflict

Change 2 will involve ERP1, 4 and 5 and during it's timeline, ERP4 and 5 cannot be impacted by any other changes at the same time. Change 4 therefore has been highlighted as a conflict as it is due to take place at the same time. It states that ERP4 is the conflict

Change 5 will involve ERP3 and 4 and is taking place at the same time as Changes 1 and 2 and so ERP3 and ERP4 are highlighted as conflicts

Change 6 will involve ERP3 but this is ok as it's not happening at the same time as Change 1

Can anyone help me with this? Looking through the various questions already raised, I can't actually see a similar request.

Thanks

Richard

David Dolch

Use a multiselect Drop Down for the Locked Applicantion

You should be able to do this using a If and COUNTIFS statement

Create a checkbox column for each ERP. ERP1, ERP2, ERP3 etc

Use this as a column formula in each checkbox column, changing the "ERP1" to reflect the ERP you are checking.

=IF(COUNTIFS([Locked Applications]:[Locked Applications], "ERP1", [Locked Applications]:[Locked Applications], NOT(ISBLANK(@cell))) > 1, 1)

This will check the box, if the ERP# value appears in the "Locked Applications" more than ones (it also ignores blanks, just to make things nice)

Now you have a bunch of columns, one for each ERP, that will be checked off if there is a conflict.

You can then add columns with IF statements to change the check boxes to texts: IF ERO 1 is checked then "ERP1" if ERP 2 is checked then +'ERP 2" etc. There are a bunch of ways to do that, either using multiple columns and then concatenating, or just use a really long nested IF statement.

David Dolch

Use a multiselect Drop Down for the Locked Applicantion

You should be able to do this using a If and COUNTIFS statement

Create a checkbox column for each ERP. ERP1, ERP2, ERP3 etc

Use this as a column formula in each checkbox column, changing the "ERP1" to reflect the ERP you are checking.

=IF(COUNTIFS([Locked Applications]:[Locked Applications], "ERP1", [Locked Applications]:[Locked Applications], NOT(ISBLANK(@cell))) > 1, 1)

This will check the box, if the ERP# value appears in the "Locked Applications" more than ones (it also ignores blanks, just to make things nice)

Now you have a bunch of columns, one for each ERP, that will be checked off if there is a conflict.

You can then add columns with IF statements to change the check boxes to texts: IF ERO 1 is checked then "ERP1" if ERP 2 is checked then +'ERP 2" etc. There are a bunch of ways to do that, either using multiple columns and then concatenating, or just use a really long nested IF statement.

Richard Castle

Yup, totally understand, thanks David. combining this with the date range will provide the view I need. I really didn't want to have to go down the route of adding multiple columns (as we have LOTS of ERPs) but this might be the only way.

Thanks

David Dolch

I was trying to figure out a way to check the entire string, so that there would not be a need for multiple helper columns, but I could not figure out a way. I feel your pain, some of my sheets have more hidden helper columns than actual useful columns my users use.

Richard Castle

No worries. Thanks for spending the time to look at this for me.

Best Regards

Rich