Capturing Data from two worksheet tabs to create import file

[Steve Marchesi]

Need some help determining the most efficient process to obtain the data required to populate an import to a CRM database using two using two separate data files.

We have a current process that is very complicated, convoluted and labor intensive so I'm looking for some help to identify an simpler process. This process needs to be undertaken anywhere from 2-4 times per year and currently consumes large amounts of time for multiple people.

I've attached a workbook with 3 tabs. The databases to extract the data from are the first two tabs labelled "SMART Detail 2016 view" & "SLX Account Export".

The import document that needs to be populated is the 3rd tab named "SLX Import Form" We do expect that there may be some blank returns related to either the "SLX ID", "TP Account" or "Sold To" fields as some may be new and may need to be created.

Our goal is to create a single combined import row/line item including data from each distinct "Sold to Name"(Column B), combined $$value for each distinct Sold To Name line/row from "Monthly Rate" (Column S), include any specific "Sold To" number (Column A) from the "SMART Detail 2016 view" tab.

We would also like to capture all potential multiple sold to #'s from Column A of the "SMART Detail 2016 view" tab for a distinct Sold To name into a single cell for later input into the "Comments" field (Column S) on the SLX Import Form.

The Green Highlighted columns on the first 2 tab/sheets are where data is typically drawn from to create a separate worksheet that would be used to populate the SLX Import Form.

The Blue Highlighted columns on the SLX Import Form are manually input and filled.
The Orange Highlighted columns on the SLX Import Form are completed as part of a later workflow process.

Please contact me for any further clarification needed and I look forward to seeing what can be developed to help improve our importing process.

Thanks,

Steve Marchesi

 

[Steve Marchesi]

I've tried to attach the .xls file with the data bases, but something is preventing me to upload - possibly a size contraint- the combined workbook is 23MB?

Is there another alternative because all content in the file is necessary?


Steve

 

[Vivek D]

Explore the Power Query addin by Microsoft. It should help automate what you are trying to do.

Also check this video.
Merge Multiple Worksheets in Workbook To New Table using Append Feature

 

[Steve Marchesi]

Vivek- I've actually truncated the size of the overall file to facilitate attachment below the 1MB size limit.

Also, is there a problem using Mozilla Firefox for a browser for the upload? I had to switch to MS Outlook to attach the file?

Thanks,
Steve

 

[Steve Marchesi]

Vivek, Will you be able to provide further guidance based on the uploaded attachment? The data sets are very minimal but provide the type of data we are working with.

I'm assuming there would be some VLOOKUP and pivot tables incorporated into a process to facilitate the extraction of required information?

Thank you,
Steve

 

[Vivek D]

You can do a SUMIF and get the Sum of Monthly Rate into the SLX Account Export sheet.

Then a pivot will get you the data in the summary form. However, I have very little idea on how you can get the comma separated sold to ids. Someone else may have to help you on that.