Auto check boxes for all rows with same vendor?

NTDSC

Hello,

We have a new organization-wide contract management system that requires a vendor profile to be created for each organization we create a contract with. In my program's contract management Smartsheet, I created a checkbox column (called "Vendor Profile in CMS?") and I started going through each row -- in this sheet there is one row for each project (contract) -- and checking off the rows where the institution already has a profile in the system.

In many cases, we have multiple projects/contracts with the same institution. So, to save time (in the long run - haha) I am wondering if there is a formula that can automatically apply the check for all rows of the same vendor. Maybe something along the lines of an IF + VLOOKUP? So where the [Contracting Institution] column says XYZ Ministry of Health, if I say yes, they have a profile and I check that box in [Vendor Profile in CMS?], then ALL the project rows with XYZ Ministry of Health will then become checked. And it will also apply the check for new project rows that we create with that institution.

Side note: For this initial search as I am going through our existing institutions to check their profiles, I am going to sort the sheet by Contracting Institution -- that way they are at least grouped together instead of organized chronologically -- but I am mainly thinking about (1) the time it takes to check off 1 box vs. 5-10, and more importantly (2) for future projects, not always having to scan through the sheet or manually search the CMS for the profile, because it will automatically check off once an institution that does have a profile is entered in.

Thank you in advance for your help!

Paul Newcome

I would suggest 2 checkbox type columns. One will be manual and the other will be automated via formula. If you put them right next to each other you will be able to see whether or not you need to both with the manual column since the automated column will have all checked based on the Institution.

Column formula for second checkbox column:

=IF(COUNTIFS([Contracting Institution]:[Contracting Institution], @cell = [Contracting Institution]@row, [Vendor Profile In]:[Vendor Profile In], @cell = 1) > 0, 1)

It says that if there is at least one row with the same Institution and the manual box is checked, then check this box. Applying it as a column formula will take care of every row even as new ones are added.

You can then pull a report where the automated column is not checked to give you a list of everything that still needs taken care of. You should even be able to work directly in the report and check the manual box there instead of having to switch back and forth between the sheet or deal with filters in the sheet.

Paul Newcome

The issue with a single column is you can only have either manual entry or a formula. If you apply the formula as a column formula, then you will be unable to manually check anything. If you apply a formula to the entire column and overwrite it with manual entry on some, you run the risk of auto-fill not picking up new rows.

You could hide the automated one and use conditional formatting to highlight those that are checked as green or those that are unchecked red (or something to that effect) regardless of the manual column.

Paul H

One possible solution:

1. Create a second sheet to act as a master list of vendors added to CMS
2. Back to your, main sheet create an automated workflow that triggers when you check the checkbox, with the action to copy row to the new masterlist sheet.
3. Add a second checkbox column with a column formula to index(match vendors on the master list.

Andrée Starå

Hi @Paul H

I hope you're well and safe!

How many vendors?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

NTDSC

Hi @Andrée Starå and @Paul H ,

Thank you for your responses. I exported the vendor list from the contract management system to Excel to make the searching a little easier, and the sheet has more than 7000 rows... But for our purposes, we are talking about a sheet with about 235 projects (currently - with maybe an average of a dozen added each year).

The CMS does not communicate at all with Smartsheet, and new vendors are being added fairly regularly (any time anyone in our organization makes a contract with someone who isn't already in the system) but there are already 7000+ vendors in the system from existing contracts, and what I'm essentially trying to do is catch our Smartsheet up with what's already in the system, if that makes sense.... So my thought was that in order to prevent us searching the CMS for each and every new contract, this will at least let us know who is already in the system and who's not -- and then once they do create their profile and we check that box, we will know going forward that we don't need to ask the partner to do that.

In the screenshot of the Smartsheet I'm talking about, I've highlighted the Contracting Institution column, and the Vendor Profile in CMS column. Each row is a different project (with a unique identifier, indicated by the NTD-SC ID# column). So what I would like to automate is the checkboxes in the Vendor Profile column for all the project rows where the Contracting Institution are the same.

For example, when I see that the highlighted XXX University does have a profile, I will check off the box in Vendor Profile, and then it will automatically check any other rows that are associated with XXX University. (I have checked them already manually, as you can see in the screenshot -- so at this point this would ideally just happen for any future projects that are added with that institution, or any existing rows where maybe the Contracting Institution is blank and we go back in and add XXX University)

Thank you again!

Paul Newcome

I would suggest 2 checkbox type columns. One will be manual and the other will be automated via formula. If you put them right next to each other you will be able to see whether or not you need to both with the manual column since the automated column will have all checked based on the Institution.

Column formula for second checkbox column:

=IF(COUNTIFS([Contracting Institution]:[Contracting Institution], @cell = [Contracting Institution]@row, [Vendor Profile In]:[Vendor Profile In], @cell = 1) > 0, 1)

It says that if there is at least one row with the same Institution and the manual box is checked, then check this box. Applying it as a column formula will take care of every row even as new ones are added.

You can then pull a report where the automated column is not checked to give you a list of everything that still needs taken care of. You should even be able to work directly in the report and check the manual box there instead of having to switch back and forth between the sheet or deal with filters in the sheet.

NTDSC

@Paul Newcome Thank you, that is a very interesting & helpful suggestion! I was hoping to be able to do this with just one column for simplicity/cleanliness, but I definitely understand that is not always possible. This might be the winner

Paul Newcome

The issue with a single column is you can only have either manual entry or a formula. If you apply the formula as a column formula, then you will be unable to manually check anything. If you apply a formula to the entire column and overwrite it with manual entry on some, you run the risk of auto-fill not picking up new rows.

You could hide the automated one and use conditional formatting to highlight those that are checked as green or those that are unchecked red (or something to that effect) regardless of the manual column.

NTDSC

@Paul Newcome Aaahh yes, that makes sense. The conditional formatting will definitely help. Thank you so much!

Paul Newcome

Happy to help. 👍️

NTDSC

Hi @Paul Newcome - I have another question for you that is (maybe) somewhat related to this earlier problem / solution you helped me with.

The Goal: I am trying to keep a running count of the countries that we have projects in.

I would have thought I could just do a "count distinct values" kind of thing, but we have some projects that take place in more than one country, and it considers those as a distinct value even if both countries are already on the list. For example, for a project in Kenya, a project in Tanzania, and a project in Kenya AND Tanzania, it would count that as 3 distinct values even though it's only 2. If ONE of the countries in a single project should be counted, for example Kenya AND Ghana, I need it to count Ghana as distinct but not Kenya. And then if there's Kenya, Tanzania, and then Cambodia AND Laos, that should be 4. Hopefully that makes sense.

So then I thought that I could do a similar thing to what you figured out above for my vendor count list, so I created two checkbox columns and started going through & checking off boxes as a new country would appear. If I got to a cell with a country that was already checked previously in the column, it would be green thanks to the helper column, so I would skip it and go to the next red cell.

This was working beautifully until I came across a couple projects with 2 countries. I thought maybe I could change it from checkboxes to numbers, so instead of "true" I could put 1 or 2, then it would count the numbers instead of just the true values or something.... But then I realized I'd still have to check back through the list of like 200 projects to see if any of those countries had come up before.

So now I guess I'm back at square one, and wondering if there is a way to count distinct values in a multi-select column and give us a running total of the number of countries that will only increase once we enter a country that isn't somewhere else on that list, including in the 2+ country projects.

(Currently to get this total I am just exporting the list to Excel and eliminating the duplicates by hand... Not ideal.)

Thank you in advance!!

Paul Newcome

@NTDSC You are going to want to create a helper column that is a multi-select dropdown type. Then you can use a JOIN function with the CHAR(10) delimiter which is a line break, and the way the multi-select works on the back end it will filter out the duplicates. Put this on a separate sheet in row 1 of a multi-select dropdown type column:

=JOIN({Country Column}, CHAR(10))

Then you would need to parse out each piece of that JOIN result into its own row. To do this, insert a text/number type column (called "Number") and manually enter numbers starting with 1 and going on down as far as you anticipate needing plus some buffer. Basically if you think you are going to have no more than 25 unique countries, go down to 35.

In the next column over (text/number type) enter this into row 1:

=IFERROR(LEFT([Multi-Select Column]$1, FIND(CHAR(10), [Multi-Select Column]$1) - 1), [Multi-Select Column]$1)

Then enter this into row 2 and dragfill down:

=IFERROR(IF(LEN([Multi-Select Column]$1 + CHAR(10)) - LEN(SUBSTITUTE([Multi-Select Column]$1, CHAR(10), "")) >= Number@row, MID([Multi-Select Column]$1 + CHAR(10), FIND("~", SUBSTITUTE([Multi-Select Column]$1 + CHAR(10), CHAR(10), "~", Number@row - 1)) + 1, FIND("~", SUBSTITUTE([Multi-Select Column]$1 + CHAR(10), CHAR(10), "~", Number@row)) - (FIND("~", SUBSTITUTE([Multi-Select Column]$1 + CHAR(10), CHAR(10), "~", Number@row - 1)) + 1)), ""), "")

NTDSC

@Paul Newcome Amazing! This worked beautifully. Thank you SO much.

Just for my own edification, would you mind explaining a little bit more about ...what the last 2 formulas are doing 😅 Specifically in terms of the different nested functions you've used and how they work.

Thank you again!

NTDSC

@Paul Newcome - Quick update: I currently have 61 rows of countries, but when I made a row to count the distinct values (so that I could link into the source sheet for a quick running total), it came up with 60. When I sorted the list alphabetically I realized that Malawi is on there twice. It is the first country on the list (in the first row that has a different formula), would that have something to do with it?

Edited to add: Additionally, one country (Angola) is missing from the country list - but it is in the joined cell in the multi-select column.

NTDSC

Hi @Paul Newcome to follow up on this issue -- I have mostly figured out the logic of the functions that you sent and how they work to parse out the joined country list, but I still can't figure out why Malawi is replacing Angola in the separated list...

You can see in the below screenshot in Column4, where I replaced the line break with "~", Angola is supposed to be listed between Gabon and Fiji (and it is also in the joined list in the far left multiselect column, which appears alphabetically). However, on row 37, Malawi is there between Gabon and Fiji instead.

I wondered if it might have something to do with those countries having the same number of characters (6), so I temporarily replaced Angola with Andorra on the source sheet. When I did that, Malawi was still in the same spot between Gabon and Fiji (so it replaced Andorra instead)... then Angola appeared later in the separated list (since there is another Angola project). Andorra was missing in the separated list and only appeared in the joined lists.

I also suspected there might be an association with Malawi being the first country on the [non-alphabetical] list and having a different formula than the rest? I tested replacing Malawi for the first project with Madagascar -- so then Madagascar appeared on row 37 instead, which I guess confirms that it's something to do with the first entry on the list. Or that the 36th country on the list is cursed.

I greatly appreciate your help with this! Very close to reaching the solution; we really have been trying to get a solid count of our unique project countries for SO LONG.