combining company data and merging/summing up data from certain columns

[bottledwater]

Hi there,

I have several spreadsheets with lists of real estate companies and their respective sales in each state. Each workbook contains data for a certain state (FL, TX, CA, etc.). Some companies had transactions in multiple states and I would like to find out their total sales and sales value in all states combined. How do I do this. I attached a sample spreadsheet that represents all the transactions of one particular state.

[GeneralDisarray]

All you need is a pivot table.

(1)
Highlight your data table (including the top, Header row).

(2)
In the ribbon, go to Insert > PivotTable

(3)
Press OK (will put the pivot table in a new sheet)

(4)
In the "Choose fields to add to report" area of the new sheet, put a checkmark next to Company Name, Number of Sales, and Sales Value.


That should do it.

[bottledwater]

I tried the Pivot Table but it wasn't able to put everything in the right order.

[GeneralDisarray]

Upload your worksheet with a pivot in place, I can take a look at it.

[bottledwater]

Thank you. It's attached.

[GeneralDisarray]

see attached - I just used a normal pivot table instead of editing your example table (don't have power pivot on the computer I'm on right now).

[bottledwater]

Nice, thanks a lot!
Is it possible to sort by amount of properties owned or sales value in a pivot table?

[bottledwater]

I don't think a pivot table is the way to go with this one. How do I add the addresses to the company names without it looking all unstructured and messy?

[GeneralDisarray]

Well, you can format pivot tables to look better if you like.


Basically, you are looking to obtain aggregates of the sales value, number of sales, and now the properties owned fields - from the data table you presented.

You have 4 options, in order of 'best practice' from my point of view.

1) Just have the aggregates pulled from the data source directly - don't even mess with the details.

2) Pivot the details table you are given to obtain the aggregates (sums in this case) - that is a very good option if the data is going to be refreshed because you won't have to redesign anything.

3) Use worksheet functions to total the values for each company - this will require you to make a distinct list of company names (every time you change the data) and then add in the functions (see example).

4) Use subtotals - this requires that you copy the data table to a normal range, sort that range by company name, add the subtotals through the ribbon, then do your sorting.