How to aggregate data

Overview

One of the most common uses of a pivot table in Excel is to aggregate data, for example calculating total sales for each person in a list:

Aggregating data in your process in SolveXia can be achieved by creating a unique list of the values you want to aggregate on and then applying a formula to calculate the sum, count or average of each value in the list. Using the example in the screenshot above, this guide will show you how to combine the “Create unique value list” and “Update Value List” instructions to aggregate data.

For the purposes of this guide, assume an Excel workbook called “Sales Data.xlsx” has been uploaded to a process in SolveXia. The “weekly sales” worksheet in the file contains:

  • A list of people, weekdays and their sales for each day (source); and
  • A space to aggregate total sales per person (target)

Steps

1. Add a “multiple file manipulations” action step to the process and select the “Create Unique Value List” instruction

2. Link “Sales Data.xlsx” as the source file and configure the column containing people’s names as the source cell range – that is Cell A2 to the last cell in column A (as per the original screenshot)

3. Link “Sales Data.xlsx” as the target file and configure cell E2 as the target cell range (as per the original screenshot)

4. Save and run the step. The target range should now include a list of unique names

5. Add a second instruction to the action step created in point 1 and select “update cell formula”

6. Link “Sales Data.xlsx” as the target file and apply a SUMIF formula in column F (to the right of the list of unique names) to calculate total sales for each name

For a detailed “how-to” on updating cell formulas click here

7. Save and re-run the step. The target range should now contain the SUMIF formula applied in step 6

Recent Posts

Leave a Comment