Removing Duplicates while also adding together values from other columns

Galadriel975

I have a bit of a complex situation. My company uses SAP for purchasing and we pull an open order report weekly to keep track of each of the buyers orders and delivery dates. I've recently set up a Data Shuttle so we can transition from manually editing the report in Excel to a more fully automated report in Smartsheet.

The problem I'm running across is that we want our buyers to be able to use the reports we create for them to write notes and enter exit factory dates for the items ordered, but SAP runs this report by line on the purchase order rather than by the PO as a whole. This means that one PO can have multiple lines depending on the sizes ordered.

We have our master sheet with all of the orders for our team, and then individualized reports for each buyer that displays only their orders.

What I'd like to do is find a way to add up the Order Quantity, Still to be Delivered, and Still to be Invoiced columns for each PO number, delete the duplicates and put those totals in the columns for that row. Or possibly leave all the data on the master sheet but only transfer the first line and tally the totals on the line that shows in the report for that PO. I've done this in Excel, but never in Smartsheet.

Is this possible to automate?

Paul Newcome

This can certainly be done. You would insert an auto-number column (called "Auto" in this example) with no special formatting and then a checkbox column (called "Use" in this example) with the following column formula:

=IF(Auto@row = MIN(COLLECT(Auto:Auto, [Purchasing Document]:[Purchasing Document], @cell = [Purchasing Document]@row)), 1)

This should check the box on the first row of each purchasing document. Then you will insert some "Totals" columns for each of the column you want to total and then use a SUMIFS along the lines of

=IF(Use@row = 1, SUMIFS([Order Quantity]:[Order Quantity], [Purchasing Document]:[Purchasing Document], @cell = [Purchasing Document]@row))

Then your report would show these totals columns and be additionally filtered to only show rows where "Use" is checked.

Paul Newcome

Are you able to provide some screenshots for context?

Galadriel975

@Paul Newcome

This is the data pulled from Excel via DataShuttle, for example, the Purchasing document 4507467423 has several lines. Ideally, what I'd like to do is condense that into one line while tallying the Order Quantity, Still to be Delivered, and Still to be Invoiced columns for those lines.

This is one of the buyer reports, which filters out the data so each buyer can see only their orders. I think the ideal solution would be to leave the data as is on the main sheet, but find a way to add up the lines and only show one line per purchasing document on the reports themselves, but I'm unsure if that can even be done.

Paul Newcome

This can certainly be done. You would insert an auto-number column (called "Auto" in this example) with no special formatting and then a checkbox column (called "Use" in this example) with the following column formula:

=IF(Auto@row = MIN(COLLECT(Auto:Auto, [Purchasing Document]:[Purchasing Document], @cell = [Purchasing Document]@row)), 1)

This should check the box on the first row of each purchasing document. Then you will insert some "Totals" columns for each of the column you want to total and then use a SUMIFS along the lines of

=IF(Use@row = 1, SUMIFS([Order Quantity]:[Order Quantity], [Purchasing Document]:[Purchasing Document], @cell = [Purchasing Document]@row))

Then your report would show these totals columns and be additionally filtered to only show rows where "Use" is checked.