• 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.

Auto Summary based on source data

Dear Sirs and Madams,

In source data, I have state names. Based on the state names, I want summary. If I add new states or delete states or modify states, in summary also state wise should be changed. I know my requirement is very odd and difficult to customize.

I attached sample data and required output. Please help me.

Regards,
Suresh
 

Attachments

p45cal

Well-Known Member
You did this using macros?
No, using the Get & Transform Data section of the Data tab of the Ribbon.

I did notice that sometimes, if you're adjusting the size of the Tables to fit the data it messes up one formula in your helper column, (if you haven't switched off error-checking, it will show as a small green triangle in the top left corner of the cell concerned) so to re-establish the correct formula throughout the column you need to re-apply the formula to the whole column by selecting the topmost cell in that column of data and double-clicking the fill-handle in the bottom right corner of that cell.

Here, I've made the table smaller and the value in the last row of the table is showing the correct value of TX104:
65582

Now if I re-adjust the table size to end at row 189 I get this:
65583

Row 181 has an incorrect value of TX108 when it should be TX104, because the formula in just that cell is looking at the whole of column Emp WL State instead of just up to that row:
65584

So just go to the topmost cell and double-click the fill-handle to put all the formulae right in that column:
65585

This applies to both your tables.
 
Last edited:

p45cal

Well-Known Member
Sir, can you please share the formula or macro which you used?
Well, it's M code in Power Query, and it can be seen if you show the Queries and Connections pane in the Queries & Connections section of the Data tab of the ribbon. You'll see 3 queries, double-clicking on any one will bring up the Query Editor, where you'll see the steps used on the right hand side. The Sales and Expenses queries are just very simple ones to prepare them for the 3rd query (Final Table) which merges the first 2 queries and loads the result to the sheet. You can see the full code for any of the queries by going to the View tab of the Query Editor's ribbon and clicking the Advanced Editor button.

If I add any new columns, will this function properly?
Not sure. I'm just beginning to get my head around this stuff myself!
I didn't write all the code, just recorded it and tweaked it in a few places.
 
Last edited:
Well, it's M code in Power Query, and it can be seen if you show the Queries and Connections pane in the Queries & Connections section of the Data tab of the ribbon. You'll see 3 queries, double-clicking on any one will bring up the Query Editor, where you'll see the steps used on the right hand side. The Sales and Expenses queries are just very simple ones to prepare them for the 3rd query (Final Table) which merges the first 2 queries and loads the result to the sheet. You can see the full code for any of the queries by going to the View tab of the Query Editor's ribbon and clicking the Advanced Editor button.

Not sure. I'm just beginning to get my head around this stuff myself!
I didn't write all the code, just recorded it and tweaked it in a few places.
Thank you Sir for your guidance.
 
What are you going to do with this 190 row table? It's not very user-friendly!
Sir, my actual source data has customer wise sales and vendor wise expenses. In "Sales by State" TX108 denotes, we have 108 customers in Texas. One customer may come more than in 1 state. Our company is filing Income Tax Return in USA where state wise revenue and expenses is required. Management did not want customer and vendor name in summary sheet. Our management reviewed this data, they asked me to shift few customers and vendors to another state. Few customers revenue is bifurcated between three or more states. Because of frequent changes by Management, I had to do the whole task many times. I spent almost 2 days on this. Later, I realized if this can be automated, I would save significant time. Then, I posted my query here.
 

p45cal

Well-Known Member
Ah right. Am I correct that in real life you don't have a helper column, instead there's a name or some such?
 
Ah right. Am I correct that in real life you don't have a helper column, instead there's a name or some such?
No Sir, In real file also I have helper column. From helper column only I got data in Summary sheet. But, mine was manual. Every time I had to sort the data whenever there were changes in state names. In real file, I have customers and vendors name source sheets.
 
Top