Collapsing rows in excel/combining data

Hello -

I'm having issues figuring out how to complete my workflow. See attached workflow and source data files as an example.

My workflow is currently taking a trial balance exported from our ERP system and we are reformatting and exporting the data into separate tabs within the output file based on:

Reporting unit company code - X-Rollup file, Sheet 2 Col. B (Col. A within rollup file correlates to Co. A within Test TB file sheet 1 tab)
Company code - Test TB excel file, sheet 1 Col. A

The output file is currently exporting as expected. The issue that I am having is that our ERP system is generating the trial balance (Test TB sheet 1 is the original unedited source data) with a separate line for balances as of 2019 and 2020 - I'm trying to update my workflow so that a single CoCode Acct# balance would only take up a single line and have a balance in the 2019 and 2020 column. For example, row 2 shows the 2019 balance for Co Code 1000, Acct # 111110 of $2,150.07 and row 3 shows the 2020 balance for Co Code 1000, Acct # 111110 of $1,453.94 - I'd like to essentially combine these rows via a vlookup type function so that the Co Code 1000, Acct # 111110 would report both the 2019 and 2020 balance in a single row.

TEST TB WF.yxmd

X-Rollup.xlsx

TEST TB.XLSX

Workflow

Accepted answers

pedrodrfaria

Hi @bradaba

You can just a summarize to combine both fields since the data is able to be grouped by other columns. See below:

And then with the rest of the workflow, you can continue with what you have as you said it is working to what you need to work.

Please let us know if you have any questions about this solution.

Please assign a solution to the post if your questions were answered. It really helps us.

Pedro.

New Workflow2.yxmd

All comments

pedrodrfaria

Hi @bradaba

You can just a summarize to combine both fields since the data is able to be grouped by other columns. See below:

And then with the rest of the workflow, you can continue with what you have as you said it is working to what you need to work.

Please let us know if you have any questions about this solution.

Please assign a solution to the post if your questions were answered. It really helps us.

Pedro.

New Workflow2.yxmd

pedrodrfaria

If you want to use a Join Logic, you can also do this.

Where we will be joining by these fields and adding the 2020 amounts right next to 2019.

Pedro.

New Workflow2.yxmd

Qiu

@bradaba
Appended some processing after your workflow and hope its what you need.

TEST TB WF.yxmd

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335