Formula to Copy cells if no match?
I'm looking for a way (probably some sort of Index/Match formula?) to check for a customer name on 2 lists. If the name shows up on the second list, but not the first, add it also to the first list.
Here is our specific set up:
The first list is a workflow tracker where most people will start. The first step in the workflow is to send a link to a form. Once the person fills out the form, their name will appear on the 2nd list. However, we will soon make this form available on a webpage, so it will be possible for a customer to find the form and fill that out first. This will make their name appear on the 2nd list, but will not yet be on the first list. Is there a way to, if the name shows up on the 2nd list (because the customer found the form on their own from the website), but it is not yet on the 1st list, automatically copy the name into the first list?
I recognize this may be a task for data shuttle/mesh, but we do not yet have this add on. I am hoping we will get it soon, but is there a formula I can use in the meantime?
Another thought is to move the form from a separate sheet into the workflow sheet. I haven't thought through this option yet, but if this seems to be the best solution, my question is: is there an easy way to duplicate forms from sheet to sheet? (I've wondered this before in other instances, so this would be a useful tool regardless)
Answers
-
Hello @vdemattei
So, you will use a lookup function that looks at one sheet and if there is an error, to look at the other sheet. Since you haven't provided any column names or functions, I'm going to try to be accurate in the functions, but let me know if you don't understand.
=IF(ISERROR(INDEX/MATCH from sheet 1)=TRUE, Lookup function for sheet 2, Lookup function for sheet 1)
If the result is still an error, you can use a checkbox helper column that points to the lookup function.
=IFERROR(lookup@row,1)
In this case, 1 just means TRUE. Depending on how frequently you refresh the data, you could set up the automation to run daily to copy rows over. I recommend it so that automations run consistently. Sometimes a trigger can be missed, especially using lookup functions.
Another way would be to use the submission form. When a new row is added, copy it over to the other sheet and you won't have to worry about watching both sheets.
-
Thanks, @MichaelTCA !
I would love a bit more help on the specific formulas. I lost you at "index/match"... I'm assuming you mean Index OR Match, not a division formula, right?
It's difficult to provide screen shots without showing personal information, but hopefully the below is enough information:
The column name on the first sheet is simply "Name" and the sheet is the "Workflow" sheet.
The second sheet is called "Screening" and the column name is also "Name".
I'm assuming this formula will have to live in a separate helper column? I don't think I want a formula to be in the "Name" column in the workflow sheet as it's the primary column and is also populate from other places.
I don't understand your last paragraph. What do you mean "use the submission form"? How would I copy it over to the other sheet? Also, it is possible that if a new name appears on the submission form, it may already appear on the workflow sheet, so I do still need to check if it's there first, before copying anything.
-
I generally write it like that because they are used together. It is not a division formula.
I re-read your thread a little bit and changed my mind. Unless you're going to use a third sheet to analyze the data between the two, then you will need the function I recommended.
On a third sheet he set up will be similar to:
=IF(ISERROR(INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)))=TRUE, INDEX({Screening-Name},MATCH(Name@row,{Workflow-Name},0)), INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)))
The 0 means you're looking for an exact match.
You will need to set up the references in the sheet before this function will work. Can't copy and paste it unfortunately.
I recommend calling the ranges something else than Name so you have an idea of which sheet the function is actually looking at. I usually enter the reference name as the "sheet - column". Ex: Opportunities - OP Value
If you have multiple criteria, instead of just 1 lookup value, the INDEX/COLLECT functions would be the way to go, although it uses more data than the INDEX/MATCH functions so if you have A LOT of cross-references, or a large sheet containing the source data, than you may want to reorganize the data within the sheets.
Smartsheet recently upgraded the amount of references a sheet can contain, so it's much harder to cause it to load slowly.
The sheet names wouldn't matter in the function directly, but it will matter when you're setting up the reference within the function. It's a popup window with all your workspaces. Easiest way to find a sheet would be to use the search bar if you have a lot going on in your account.
Another option, would be to use a checkbox column (this is our helper) in the Screening Sheet. The function would be a combo of the two functions I suggested above. Since we already know it's contained within the second sheet.
=IFERROR(INDEX({Workflow-Name},MATCH(Name@row,{Screening-Name},0)),1)
The first option would be good if you have more analytics involved, such as a count of how many people are contained in one sheet and not the other. Having a separate sheet specific to the analytics is easier to manage and report aggregate data, like a one stop shop.
The second option would be better if you only need to use the sheet and all the data you need to use or report is straight forward with minimal aggregate data.
It all depends on how you want to collect, review, and present the data.
-
Thanks for your response, @MichaelTCA. I don't think I need a 3rd sheet, as I don't have a need for additional analytics. I simply want to make sure if any name is in the 2nd sheet, but not the 1st, that it copies to the 1st. That being said, I think the checkbox could work.
I entered this formula:
and I'm getting #UNPARSEABLE. I'm guessing it has to do with my column name, "[Name]"? I had to manually type it in instead of selecting the column. I don't know - I'm very much a novice at this stuff.
Once I can get this formula to work, I'm assuming I'll use an automation to copy row to the other sheet IF the check box is checked? I think I can work with that.
-
Use this function in the source sheet and reference the range in the target sheet.
=COUNTIF({Range of Values in Target Sheet}, [Value in Source Sheet]@row)
This will count how many of that value is contained within the target sheet.
The automation will have a condition that this helper column needs to equal 0. (Value is not contained at all in the target sheet)
I run the automation on a schedule and I think this is the easiest way, so if any cells in the helper column have a 0, then it will be copied over to another sheet. If it's not 0, then it's already on the sheet and the condition will prevent the automation from copying duplicate rows.
-
@MichaelTCA I really appreciate you trying to help, but I'm really not following. Can you help with the formula in the screenshot of my last comment? I don't understand how your last comment is related.
-
Change the range in the Match function to [Name]:[Name].
Then use the countif function to verify whether the information is on the other sheet. You can still use a checkbox.
-
I updated the formula to use "Name:Name" instead of "[Name]" to reference the column in the screening sheet, where I put this formula. It is now bringing back values, but they are random names from the workflow sheet. I was expecting this formula to instead check the box if the name was not found in the workflow sheet - something isn't quite working right.
-
Oh, it looks like it's searching for the name in the same sheet, instead of the workflow sheet. I think the workflow Name and the screening name need to be switched in the formula you suggested.
-
Alright, I was able to get it to work. Thank you!
Unfortunately, I still have lots of other issues I need to figure out. Since I'm using peoples' names to match, I'm finding many times someone with the same name has it entered differently in the 2 forms, so it's coming back as an "error", even though that person does exist in both lists. I use ID numbers after this point in the process, so I don't have this issue moving forward, but this is the very first contact before an ID number is assigned, so I'm not sure of a way around this issue... I'll have to keep thinking on this one.
-
So it worked after switching around the references?
The IFERROR function will check the box if the value isn't found within the source. Is this working?
I've also had to use ID #s. Especially with customers.
I set up an autonumbering system so that an ID is assigned when a person is entered into the list. Then I don't have to worry about people who somehow made it to the list multiple times. But yes, this is an issue for me as well. The "quick fix" was to assign IDs, but then everyone needs to know their ID and that throws another twist in the mix.
-
Yes, the formula itself worked once I switched the references.
I'm totally with you on the ID and that's how we typically operate, but this form can be found from a public website, so there's no opportunity to provide an ID number yet... However, I remembered a different work around. I will give the website a unique URL so that when the form is filled out from the website, I'll know and can automatically add them to my workflow list when they come in from that unique URL. I forgot that you can do that!
https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
-
@vdemattei Oh cool, you're getting into more complicated features. You'll be writing code with API calls in no time.
-
Ha! Well thanks... I'll need to figure out what that even means first haha!
Help Article Resources
Categories
Check out the Formula Handbook template!
