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

Leave a Reply

Latest Tweets

Free eBook: 10 Tips for sourcing good data yucalc.com/2017/02/17/fre…

reply · retweet · favorite

Newsletter

By signing up, you agree to our Terms of Service and Privacy Policy.

Address

Suite 1506, Westfield Tower 2, 101 Grafton St Bondi Junction NSW 2022 Australia

View Larger Map

Contact Us

AUS: +61-2-9386-0202
UK: +44-20-8123-1251
Send us an email

ContactUs.com
WordPress Image Lightbox