How to Automate workflow to create a summary report to diff between two excel sheets on weekly basis

Ashraf N.

Using SmartSheet, I want to be able to import excel sheet from sharepoint on weekly bases 
lets say "week 09" excel sheet name is [TC Roadmap 25ww09 VTS] 
Then I need to add to end additional +5 columns, these column includes formula(s) 
which I use to massage data coming from original data to create reports
After  a week , now "week 10" I want [TC Roadmap 25ww10 VTS] gets copied to same workspace of
 "week 09" and again to add same additional +5 columns to the end , 
these column includes formula(s) .. I want to compare "week 09"  to "week 10" 
and create Summary Report for changes in all cells/rows by printing entire row 
and showing what have changed (add/delete) in that week , compare except in additional columns
which were added after import. Since this is repeatable on weekly basis, it will be great 
if it can be automated in workflow (import, add columns, diff) and creating weekly difference in summary report also in dashboard.
Let me know if you can help?

kowal

hi @Ashraf N.,

Yes you can do that by using DataShuttle first you import the Excel9 to Smartsheet which has those 5 columns already there to make calculations. U use DataShuttle again to export the Smartsheet to Excel10 with the proper calculations.

Seems like pretty easy setup. You just need to have DataShuttle.

Hope this helps.

Scott Peters

Hello @Ashraf N. - I recommend using a central sheet that contains both weeks of data, something like this. It can also be done using formulas with cross-sheet references, but that creates busy work linking in the new sheet each week. Here's an example of how I do this:

1. Use Data Shuttle or a Copy Row workflow to add in the new week of data
2. Use two Sheet Summary Fields to declare that LAST WEEK = Week 09 and THIS WEEK = Week 10. Adjust these each week or you could develop formulas so they auto-adjust based on date or day of week
3. For each column you want to compare, add a helper column to determine if the data between the two weeks is same or different. If it's just whether the values are different, I like checkbox columns. If you need to determine if a value went up or down (like a sales number or quality %), you would use a text or symbol column
4. Use conditional formatting to highlight your data if the helper column is true
5. In your report, filter for only the current week
6. In your compare sheet, add a workflow to move rows that are older than last week

Here's an example, I hope this helps!