Data migration Question

Bec1

So I have what feels complex to me but maybe not for a super user...

Sheet is collecting information from various customer service reps in regards to payments they collect.

This is collected via a form they fill out at the end of their shift.

Then I need to reconcile. I've got the system we us generating a report in Excel format.

Currently I manually enter this data into the SS to reconcile.

2 questions--How to I change my sheet to mirror the way the excel data is coming out. Each person in excel has a line for each specific type where in my SS it is one row per user each date.

Is there a way to auto pull the excel data daily into the sheet?

Thanks in advance!

Bec1

It is a daily reconciliation. I think I may have resolved my issue with data mesh though. :)

I have a new question to post now LOL

Emily Zeiger

I'm not sure I understand question one but for question two, I import sheets from excel then select all rows and move all rows to the end smartsheet - this does require column headers to match to grab the data and place it where it needs to be. Hopefully that helps, it can be a bit of work but for large work flows with recon, this has worked well for us (banking industry)

Bec1

Are you able to auto pull the data from excel or have to manually do it each time?

Are you willing to share any sort of reconciling template you use?

Paul H

I use Datashuttle add-on to automatically upload CSV reports to Smartsheet and reconcile against Smartsheet data.

https://www.smartsheet.com/marketplace/premium-apps/data-shuttle

Andrée Starå

Hi @Bec1

I hope you're well and safe!

I recently developed a data Import solution. How many rows of data in each time from Excel?

If you have access, Data Shuttle would be the best option.

Would that work/help?

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!

Bec1

Hi Andree!

The amount of rows will vary each day depending on the number of users on site that collected money.

I'll try to explain a little better.

I created a sheet that has 28 columns. Here's a preview;

The sheet has columns for each payment type within a group, a subtotal for the payment type and then grand totals.

IE: Visa + Amex + MC +Credit Cards Subtotal

patient check + online patient pyment = Subtotal Direct Deposits

Subtotal CC = Subtotal DD = Grand Total

There is a grand total for each user within the same day. (one row per user)

The data is collected thru form submission from the employee.

Then the billing manager reconciles the data by comparing to the report that the EHR generates. I can tweek the report a little with crystal and get a data output in excel.

Currently she takes that excel report and manually enters the data into another column on the sheet. Then the sheet has an automation I put on to compare the totals.

I'm trying to get away from the manual data entry portion she does and want to somehow just populate that column. I can't seem to wrap my head around the best way to do that. I've been playing with some different sheet formats too to try and get the best fit....ugh I need help.

Keep in mind I've only been using SS for about 6months so that may play a role in my ignorance.

Best,

Rebecca

Andrée Starå

@Bec1

Happy to help!

Do you have anything unique that we could use to match the records?

Bec1

Not sure I follow your question.

Andrée Starå

@Bec1

I think I misunderstood.

Do you only need to add data to the Smartsheet sheet from Excel on a running basis?

Bec1

It is a daily reconciliation. I think I may have resolved my issue with data mesh though. :)

I have a new question to post now LOL