Excel manipulation

[bradaba]

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 

[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. 

[TonyA]

You don't need to do that. I used a Summarize tool grouping by Trans.Type and BOL and counted the distinct combinations. There were a lot of counts of 1 and 3, but I also saw quite a number of 5's, 4's, and 2's. I assume you want to sum the values for Quantity and Total Value if the number of rows is 3, but I don't know what you want to do for the others.

[bradaba]

@TonyA Correct, I am looking for the sum of the Total Value and Quantity based on Trans. Type and BOL - I guess I am not concerned with the "counts" that are showing up. I am simply looking for the sum, whether that's adding 2, 3, 4, or 5 (or any number) of rows. 

Another thing I can't get my head around is how to get the summarized data back in the format of the original source file. For example, Purchase161076 has 5 rows. Outside of the Quantity and Total Value columns, all the rest of the data is the same (we can remove the last "Payable Matching Status" column. Is it possible to summarize this data and keep the rest of the columns from the original data? 

[TonyA · Accepted solution]

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.