compiling data

[aalexand]

Hi All, 

I am needing to combine data from two different worksheets into one into a specific format so that our CRM can update records.  Basically, we have contracts at our company, and this is the way we activate members to be able to access these contracts.  On Spreadsheet 1 we have a list of the contract numbers that are being executed.  The number of contracts being activated could be a few or it could be 1000's depending on the upload.  Spreadsheet2 is a list of the locations that the contracts are being activated for, the list of the number of entity codes will also vary as previously state with the contracts.  What we have to do is build a spreadsheet that associates each entity code with each contract number and add an action column of 'Add'.  This then gets loaded into the CRM and activates each entity onto each contract listed.  I was hoping to find some help on how this would be best achieved using power automate?  I have done 100's of searches and read forums and can't find anything that makes sense to me on how to accomplish this. Any help would be greatly appreciated!!

[trice602]

Hi @aalexand ,

If this fell in my wheelhouse, I wouldn't use Power Automate for combining the data.  I would use XLOOKUPS, just regular old excel formulas, OR option #2 would be to go through Power Query but it still doesn't require Power Automate.

What you are describing can be accomplished with either or both, and essentially you are populating an upload template for your CRM.  Does that make sense?

[Agnius · Accepted solution]

Here's a sample flow that would do this for you.

This part gets the data:

This part manipulates it to create a single data table:

Note that when you use Create new data table, it creates it with a single empty row by default, and thus using Delete row from data table is necessary to remove it.

And this part writes it all into a new Excel file:

This is assuming you don't have some template and just need a blank file opened, populated with data and saved.

PAD can write an entire data table via a single Write to Excel worksheet action. But the same also needs to be used to write the headers.

Here's a code snippet you can copy and paste to your PAD designer to get all of these actions:

SET FilePathEntities TO $'''Some file path'''
SET FilePathContracts TO $'''Some other file path'''
SET FilePathOutput TO $'''Where you want your upload file saved'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePathEntities Visible: True ReadOnly: False Instance=> ExcelInstanceEntities
Excel.GetFirstFreeColumnRow Instance: ExcelInstanceEntities FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstanceEntities StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelDataEntities
Excel.CloseExcel.Close Instance: ExcelInstanceEntities
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePathContracts Visible: True ReadOnly: False Instance=> ExcelInstanceContracts
Excel.GetFirstFreeColumnRow Instance: ExcelInstanceContracts FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstanceContracts StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelDataContracts
Excel.CloseExcel.Close Instance: ExcelInstanceContracts
Variables.CreateNewDatatable InputTable: { ^['Action', 'Entity Code', 'Contract'], [$'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
LOOP FOREACH CurrentContract IN ExcelDataContracts
    LOOP FOREACH CurrentEntity IN ExcelDataEntities
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Add', CurrentEntity['Entity code'], CurrentContract['BID CONTRACT NUMBER']]
    END
END
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstanceOutput
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Action''' Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Entity code''' Column: $'''B''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Contract''' Column: $'''C''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: DataTable Column: $'''A''' Row: 2
Excel.SaveExcel.SaveAs Instance: ExcelInstanceOutput DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: FilePathOutput
Excel.CloseExcel.Close Instance: ExcelInstanceOutput

You will obviously need to set actual file paths in the first three actions.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[aalexand]

Thank you Agnius.  Only issue I am having is on line 16 it is having an error saying entity code doesn't exist.  Any ideas?

[Agnius]

I assumed your input Excel files have the same column names as those shown in the screenshot. If they do not, you need to modify the column names used in line #16 like so:

Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Add', CurrentEntity['{ColumnNameForEntityCode}'], CurrentContract['{ColumnNameForContractNumber}']]

 Where {ColumnNameForEntityCode} is the column name where the entity codes are stored from the entities worksheet, and {ColumnNameForContractNumber} is the column name where contract numbers are stored from the contracts worksheet.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[aalexand]

Dumb mistake on my end.  I was pulling the data from the 2nd row instead of the first row so it wasn't pulling in the headers.  Thanks for all of your help on this.  Really really helpful and very much appreciated!