Excel manipulation

Hello All -

I'm struggling to prepare a workflow to accomplish what I'm looking for... I've attached a TEST excel file for reference. I am trying to accomplish the following:

1. For Transaction Types "Sales" and "Purchases", there are scenarios where there are 3 duplicate BOLs (Col. E). If this is the case, there is a positive amount, a reversing negative amount, then another amount which is the resulting amount of the transaction. I would like to consolidate the 3 rows into 1 row - BUT, I need to only do this for Trans. Types "Sales" and "Purchases", I don't want to distort any of the other transaction types data.

2. I'd like to export the data to their own tabs, within the original file as follows:

Trans. Type "Sales" and "Purchases" - both of these transaction types would end up on the same tab, based on the "Title Transfer State" (Col K)
Trans. Type "Tax-Federal Perc" ends up on it's own tab named "Fed PERC"
Trans. Type "Tax-State Perc" ends up on it's own state tab, based on the state in "Title Transfer State" (Col. K)
Trans. Type "Tax - Washington State Excise (AR)" ends up on it's own tab named "WA State Excise"
Trans. Type "Tax Canadian GST" ends up on it's own tab named "Can GST"
Trans. Type "Tax - ND Motor Fuel (AR)" ends up on it's own tab named "ND Motor Fuel"

Thank you

TEST.xlsx

Workflow

Accepted answers

TonyA

You can summarize and keep all the values. Just group by all the rows you want to keep.

I've attached two workflows. One isolates and summarizes only rows where the combinations occur exactly three times (because I built that before I saw your last message.). The "v2" version summarizes all the rows for Sales and Purchase grouped by all relevant columns. I had to drop Payable Matching Status from the Purchase/Sales rows because they differed for the same BOL values. I also added the sheet names and bursting the output to multiple sheets.

I added these as yxzp files, so just download them and double click and Designer will unpack and load the workflow with the input file.

EDIT: By the way, the Provision Description can also vary with the same combination of Trans. Type and BOL. So you are actually grouping on three fields, not two.

Excel Manipulation v2.yxzp

Excel Manipulation.yxzp

All comments

TonyA

I'm seeing cases where there are anywhere from 1 to 5 rows with the same Trans. Type and BOL for Sales and Purchases. How do you want to handle BOLs where the number of rows is not 1 or 3?

bradaba

@TonyA I'm thinking it may be easier to add a column which concatenates the Trans. Type + BOL to create a unique identifier. Then I could do a Summarize view of the Concatendated column summing the Total Value? If I did this, I can't figure out how to get the summarized view back into the original data file. This contradicts my original #1, however, I think this is what I'm looking for. Appreciate your help.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703