• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summarizing data without pivot tables

Y

Yvonne Love

Member
I have data that I need to have summarized for some analysis (charts, tables, graphs, etc.), and I don't want to use pivot tables for a few reasons (I like using SUMIFS formulas and structured references in tables, and pivot tables cause the spreadsheet to be larger in file size - and my original file is already going to be large due to the amount of data).

I've attached a sample of the data in the format it is exported in (columns P through the end contain values I have looked up from other tables as there is no one report I can run that gives me all of this data).

From data like this, I often have to summarize top clients by Location (column K) or Region (column T) or Broad Type of Law (column R) or Timekeeper (column O) (among other summarizations). I'm comfortable with array formulas, though admittedly there are so many variables, I haven't tried to create one on my own. And array formulas can often slow down the spreadsheet even more. So by using non-array formulas and no pivot tables, is there a way, for instance, to pull the top 10 clients by location for 2017 from just the table provided? This trick, if there is one, has eluded me.

Thank you!!
YL
 

Attachments

  • Test Data 080917.xlsx
    577.7 KB · Views: 11
  • Test Data 080917.xlsx
    577.7 KB · Views: 8
Hi ,

Can you upload your workbook with layouts for what the output should look like , for your various requirements ?

Narayan
 
Here it is with a simple summary of what I am trying to do. I did add a pivot table on the PT tab, and then on the Houston tab, I have the simple summary.

I also changed the amounts in Column F to be random, unique amounts.

Thanks!
 

Attachments

  • Test Data 080917.xlsx
    772.3 KB · Views: 17
Hi ,

Are you saying that you want the summary as posted on the tab named Houston , through formulae ?

What would be the user interaction in this ? What variables should the user be able to change ?

Narayan
 
If possible, I would like to be able to summarize the data as it appears on the Houston tab without the use of pivot tables or array formulas on the master data sheet. The variable would be changing the office from Houston to another office and having it automatically update accordingly. I am not sure that this is even possible or not, so I have the tools to do it the way I have been doing it, but it just means updating takes longer.

Thanks.
 
Hi ,

A pivot table is the fastest way to summarize data in Excel , since it is native to the software ; any formula or VBA code is a layer imposed on top of Excel , and is bound to be slower than a pivot table.

Secondly , I doubt that array formulas will be the problem ; what ever formula is used will have to operate on data arrays , and just the fact that a formula has to be entered using CTRL SHIFT ENTER is not a sign that that formula will be more time consuming in calculating.

Narayan
 
Thanks Narayan. With the amount of data I have, arrays tend to be slower and always seem to recalculate when I do anything. But I do know the formulas I need to use if I choose that route. I didn't think there was a way to summarize based on the raw data without pivot tables, but I also know I don't know everything about how Excel works.

I really appreciate you taking the time to look into it. Thank you,
Yvonne
 
Thank you very much! I like this approach a lot and I will definitely incorporate it into my worksheets. On Test Data tab, column U, that formula is perfect. I have an array formula that produces the same results, but with the amount of data I have (often over 100,000 rows), the array formula slows things down. I will try this formula and let you know if using the same number of rows it has the same impact as the array formulas.

Thank you so very much!!
Yvonne
 
You must log in or register to reply here.
Back
Top