Identify Duplicate/Unique Values in Two Sheets

John C

Hello,

I have questions regarding the ability to identify duplicate and unique values from two sheets.

So, I have two sheets. 

Sheet A = Membership list. This sheet includes columns for:

• Name, 
• Email, and 
• a checkbox for whether the team member is required to fill out a weekly status report.

Sheet B = the weekly status report. This sheet is updated via a form and includes columns for:

• Name, 
• Week ending, and 
• the report details.

Currently I am executing the following process weekly:

1. Export both sheets to Excel
2. Copy the Names column from 'Exported Sheet B' (weekly status report) for the most recent week and paste it into a new column in 'Exported Sheet A'. 
3. In Excel select 'Conditional Formatting > Highlight Cell Rules > Duplicate Values' to identify the unique values and more specifically, those who have NOT completed the report.
4. Manually email individuals identified in the previous step and request that they complete the status report.

So, here are my two questions:

1. Is there a way to move this process to Smartsheet? I'm struggling to find a way to identify unique values.
2. If there's a way to move it to Smartsheet, is there a way to automate this in Smartsheet?

Thanks for any insights you might offer!

Heather Duff

Hi @John C ,

I recently set something similar up to compare two sheets. What I did was add a flag column to my sheet (likely you would use Sheet A) with a cross-sheet formula. You could probably do the same, doing something like this:

Add a date column in Sheet B to calculate the week number of the Week Ending column. It would be as simple as

=WEEK([week ending]@row)

Then add a flag column in sheet A to flag people who have not done their weekly update, but are required to:

=IF([checkbox for whether they need to submit a weekly update]@row=0,0,IF(COUNTIFS({range name for Sheet B names column},[name column in sheet A]@row,{range name for sheet B week ending date},WEEK(TODAY()))>0,0,1))

A few notes on this:

• I am assuming your Week Ending column is set up as a date column. If it isn't, the formula may need to be adjusted.
• I am also assuming the checkbox for whether they need to submit a weekly update is set up so that checked means yes they need to, and unchecked means no they do not.
• This will flag those who have not yet submitted an update for the current week. If you want to flag for last week instead, replace TODAY() with TODAY(-7).
• The names entered in sheet B will have to match what is listed in sheet A exactly; otherwise, it won't recognize a match between the two sheets.
• Of course, you'll have to update all column names and range names in my formula.

Let me know if this works for you!

Best,

Heather

Leibel S

@John C

Add a field into Sheet A that pulls in the latest weekly status report from Sheet B.

you could then see when was the last time someone summitted a report...

the exact formula would depend on the setup and columns you have.

Example A: (assuming week ending is a date field)

=MAX(COLLECT({Week ending range} ,{name range},Name@row))

Heather Duff

Hi @John C ,

I recently set something similar up to compare two sheets. What I did was add a flag column to my sheet (likely you would use Sheet A) with a cross-sheet formula. You could probably do the same, doing something like this:

Add a date column in Sheet B to calculate the week number of the Week Ending column. It would be as simple as

=WEEK([week ending]@row)

Then add a flag column in sheet A to flag people who have not done their weekly update, but are required to:

=IF([checkbox for whether they need to submit a weekly update]@row=0,0,IF(COUNTIFS({range name for Sheet B names column},[name column in sheet A]@row,{range name for sheet B week ending date},WEEK(TODAY()))>0,0,1))

A few notes on this:

• I am assuming your Week Ending column is set up as a date column. If it isn't, the formula may need to be adjusted.
• I am also assuming the checkbox for whether they need to submit a weekly update is set up so that checked means yes they need to, and unchecked means no they do not.
• This will flag those who have not yet submitted an update for the current week. If you want to flag for last week instead, replace TODAY() with TODAY(-7).
• The names entered in sheet B will have to match what is listed in sheet A exactly; otherwise, it won't recognize a match between the two sheets.
• Of course, you'll have to update all column names and range names in my formula.

Let me know if this works for you!

Best,

Heather

John C

@Heather D This is great... I'm going to try that out this afternoon to see if I can get it to work. I'll let you know how it goes.

Thank you!!

Best,

John

John C

@Heather D Okay. I've had some time to poke around and try this out. I'm able to create the formulas with no errors, but they are not flagging appropriately and I'm not sure why.

Sheet A - Membership List

I created a column called FLAG using this 'Column Formula':

=IF([Required to fill out status report?]@row = 0, 0, IF(COUNTIFS({B Status Report Range 1}, [New Team Member's Email]@row, {B Status Report Range 2}, WEEKNUMBER(TODAY()) > 0), 0, 1))

WHERE {B Status Report Range 1} = the names column on Sheet B and

WHERE {B Status Report Range 2} = the Week Ending column on Sheet B

Sheet B - Status Report

I've created a Week Number column using this 'Column Formula':

=WEEKNUMBER([Week Ending]@row)

--

As a note, I've ensured that both the [New Team Member's Email] column in Sheet A uses the exact same values as the [Team Member] column in Sheet B.

I've also updated the TODAY() function in Sheet A to use values of -7, -14, and -21, but I cannot get the flags to change as I expect them to.

Lastly, the Week Number column already has values that range from 6 to 18.

Heather Duff

Hi @John C ,

I've made a minor tweak - maybe this will help the formula:

=IF([Required to fill out status report?]@row = 0, 0, IF(COUNTIFS({B Status Report Range 1}, [New Team Member's Email]@row, {B Status Report Range 2}, WEEKNUMBER(TODAY())) > 0), 0, 1)

Let me know if that fixes it - I think it just had a parenthesis in the wrong place.

John C

I had tried that yesterday as well - it responds with an #INCORRECT ARGUMENT SET message. @Heather D

Leibel S

@John C

The correct formula syntax is:

=IF([Required to fill out status report?]@row = 0, 0, IF(COUNTIFS({B Status Report Range 1}, [New Team Member's Email]@row, {B Status Report Range 2}, WEEKNUMBER(TODAY())) > 0, 0, 1))

John C

Thanks @Leibel S ! That makes the errors go away for certain, but I'm unfortunately not getting the information I need still.

For some reason, regardless of what I mark the TODAY() function to say, the flags do not change.

I tried

=IF([Required to fill out status report?]@row = 0, 0, IF(COUNTIFS({B Status Report Range 1}, [New Team Member's Email]@row, {B Status Report Range 2}, WEEKNUMBER(TODAY(-80))) > 0, 0, 1))

and still no change in the results.

Leibel S

Is the name column on sheet B ({B Status Report Range 1}), the same format and setup as [New Team Member's Email] column on sheet A?

John C

Yes. They are exactly the same. In fact, all of the names are being 'Flagged' as having completed the report, except for those not checked in the "Required to fill out status report?" column.

Leibel S

@John C

John,

The formula only flags those that have not completed the report....

Heather Duff

@Leibel S Eureka! That could explain why the flags don't change - perhaps those people did not complete the report on the other weeks John's changed the WEEKDAY(TODAY(-#)) number to...

John C

Got it... Something else must be amiss then as the flags don't change when I change the TODAY parameter.

Last week I had the majority of individuals complete the report and the flag is still checked.

Maybe it's a problem with how I'm getting the WEEKNUMBER?

Thank you for your patience with me as I try to work through this.

Heather Duff

@John C I love solving puzzles! Let's keep at it. Is your [week ending] column a date column?

John C

Yay! I'm so glad and thankful.

Yes. My [week ending] column is a date column. It was originally populated via a dropdown of dates, but I had to change it to a date column to make the WEEKNUMBER function work.

Once I move this to "production" I will change the reference from the [week ending] column to the auto-generated "Date Created" column. I created the week ending column because my sample data was all the same date when I created the sheet.