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

Pivot table number Grouping

Tauqeer

Member
Hi

I am attaching this file, I have done some countifs analysis in the analysis tab and tried
to do the same with Pivot table in under pivot tab.

Question1: Is there any better way of doing countifs instead of writing these long codes.
Question 2. When grouping the amount finance from 0-75k, 75k-150k , it doesn't allow to
group from 150-300k as if I do that it changes the whole grouping, please advise if there
is any way to group in to 04 categories as 0-75k, 75-150k, 150-300k and 300k-500k.
Question3: when I select different slicers the column width always changes , is it possible to fix the pivot table columns width as fixed.


please advise if its possible.
 

Attachments

  • Copy of New AF data Feb.xlsx
    711.9 KB · Views: 22
See attached.
Re. countifs formula: Not addressed; there might be a way but they're not that bad - do you need them now that the other points have been addressed?
 

Attachments

  • chandoo33868New AF data Feb.xlsx
    904.2 KB · Views: 7
@Tauqeer
Please do not create duplicate posts, even within the same thread. It would be good if you reviewed the forum rules so as to avoid breaking them again.
 
See attached.
Re. countifs formula: Not addressed; there might be a way but they're not that bad - do you need them now that the other points have been addressed?


Hi P45cal
Thanks a lot for your help, really appreciate your time and support.

few things I need to check with you.


- Can we change the Sequence of grouping .

- Can we change the Sequence of Slicer as I think it picked up the sequence from the amount group.


As 75-150k comes in the last , it should have been come at the 2nd after 0-75k , is it possible to change the sequence in the amount grouping.so the Sequence should be

like 0-75k
75-150k
150-300k
300-500k


- Also if I want to import new data every month then what would be the ideal
way to keep this as template , so I can cut and paste the new data here and
it keeps the helper column as it is to run vlookup for group ranges and when
I delete the old data it wont delete the helper col and the formulas, any suggestion to keep this as a template and keep on changing the data.

thanks

Tauqeer
 

Attachments

  • chandoo33868New AF data Feb.xlsx
    771.7 KB · Views: 3
Can we change the Sequence of grouping .
Yes, using custom lists:
  • For Excel 2010 and later, click File > Options > Advanced > General > Edit Custom Lists.
  • For Excel 2007, click the Microsoft Office Button Office button image > Excel Options > Popular >Top options for working with Excel > Edit Custom Lists.
  • In the Custom Lists dialog box, verify that the cell reference is of the list of items (sheet Raw Data range Q4:Q8 in the file I attached - you can select them with the mouse) is displayed in the Import list from cells box, and then click Import.
  • Close down the dialogue box and go to the pivot and either refresh and/or in the FinGroup header, choose sort A-Z.
Can we change the Sequence of Slicer as I think it picked up the sequence from the amount group.
Not sure which slicer you're talking about; if you've added a FinGroup slicer it should automatically be sorted using the custom list you've added above. If it's the Amount_Financed slicer, that can be sorted ascending or descending - I will need some clarification from you here if you need more.

Also if I want to import new data every month then what would be the ideal way to keep this as template , so I can cut and paste the new data here and it keeps the helper column as it is to run vlookup for group ranges and when I delete the old data it wont delete the helper col and the formulas, any suggestion to keep this as a template and keep on changing the data.
Where is this data coming from? It may be possible to get the pivot table to look at external data directly (that data can be saved in the cache (I think) so the workbook would still be usable if you chose to send it to someone who doesn't have the external source data).
 
Last edited:
Hi P45cal,

Thanks a lot for solving my problem, I have arranged the custom list, now it looks fine, with regards to last issue, I receive data every month in form of excel file with same columns and fields as in the spreadsheet. I don't have access to original database where it comes from, so once I receive the spreadsheet I have to run this pivot table , so what are my options to use new data every month.

Either to clear the data from the this sheet and paste new data, so when I clear
the sheet it will remove the helper col formulas as well or is there any way to keep the formulas intact , may be a macro to clear all values and keep the helper col formulas as it is , so when I paste the new data it will automatically calculates the fingroup an feeds that into the existing pivot and we can refresh the pivot to get new data.

Other issue could be when the sheet is empty after clearing the data and if someone accidently refresh the pivot I will break the pivot as there will be no data and fingroup calculated and pivot will show errors, is there a way to stop
refreshing pivot if there is no data in the sheet.

I really appreciate you time and help on this one.
 
Can you attach such a data file? Without comments/added rows, so that it's as real as it can be - sure some search/replacing to desensitise the data, but not wholesale so that it becomes unrealistic. Does this data file have more than one worksheet (tabs) in it?
 
File is exactly the same as raw data sheet,
And it doesn't have any other tabs , just one
Sheet, so just have to replace the raw data
Every month and refresh the pivot table,
So looking for a best option to do it,
Perhaps with some macro ..
Is there any macro possible to stop the pivot
Table to refresh when the sheet is empty.
 
In the attached, there's a new button on the Pivot Table sheet labelled New Raw Data, the idea is to click it, then double-click a data file, but it is very unlikely to work out-of-the-box. If you click it you will likely get this at some stage:
upload_2017-4-14_23-31-38.png
It doesn't matter which button you click on here, but click OK.
To rectify this you need to go to the Raw Data sheet and right click somewhere in the table and choose Table|Edit Query… :
upload_2017-4-14_23-37-46.png

and you'll get a dialogue box like:
upload_2017-4-14_23-40-35.png
This is where you edit (once and for all if the sheet name is the same in all raw data files (if they aren't, then we'll have to think again!)) the name of the sheet in the new data file; The rules are as follows:
1. Use the new data file's sheet name followed by a dollar ($) sign.
2. If the sheet name has a space anywhere in it, use single quotes around 1.
3. If the sheet name does not have a space in it, remove the single quotes.
You shouldn't need to alter anything in the upper Connection: box.
Click OK. The data table should refresh with new data if you get it right.

Now try clicking the new button on sheet Pivot Table again.

Note that the data in the data files should be right at the top of the sheet. If it's not, come back and we can correct for it (I think).

Clicking the button should also refresh the pivot table.

I've converted the Raw Data sheet's table to a real Excel Table which has a query in it to import data from an external file. That table has a formula in the rightmost column which remains in place, and adjusts its extent with the size of the table automatically, which is the FinGroup formula with the lookup table hard-coded into it.
 

Attachments

  • chandoo33868New AF data Feb.xlsm
    41 KB · Views: 3
Hi

Thanks for your reply, I am really confused now, when I press the new button, it opens my document folder and I select the raw data file, but it comes with run time error.

also please advise your last point as I didn't get it, how do you select the vlookup range in this. please advise.

"I've converted the Raw Data sheet's table to a real Excel Table which has a query in it to import data from an external file. That table has a formula in the rightmost column which remains in place, and adjusts its extent with the size of the table automatically, which is the FinGroup formula with the lookup table hard-coded into it."
 
also please advise your last point as I didn't get it, how do you select the vlookup range in this. please advise.

"I've converted the Raw Data sheet's table to a real Excel Table which has a query in it to import data from an external file. That table has a formula in the rightmost column which remains in place, and adjusts its extent with the size of the table automatically, which is the FinGroup formula with the lookup table hard-coded into it."
That table has a formula in the rightmost column which remains in place, and adjusts its extent with the size of the table automatically, which is the FinGroup formula with the lookup table hard-coded into it.
The formula in column N of the Raw Data sheet used to look like this:
=VLOOKUP(I2,$P$4:$Q$8,2,TRUE)
where I've highlighted the bit that refers to the lookup table in green.
Below a screenshot of what it used to look like, with the lookup table highlighted in green:
upload_2017-4-15_10-1-15.png

I changed it so that we no longer needed a lookup table by hard-coding the look up table into the formula (the look-up table (which is no longer a table) again in green:
=VLOOKUP([@[Amount_Financed]],{0,"0-75k";75000,"75k-150k";150000,"150k-300k";300000,"300k-500k";500000,">500k"},2,TRUE)

The bit in red is the result of converting the table to a real Excel Table, and is equivalent to I2 in the old formula.

I don't know how I can make it any clearer.

Aside from that, I tried your recently attached Raw Data.xlsx, whose only sheet is called Sheet1, followed my own instructions about changing the sheet name using Edit Query… and it worked fine:
upload_2017-4-15_10-13-5.png
I attach a version with that change made for you (although, to reduce size I manually deleted several thousand rows from the Raw Data sheet).

So once again, what did the error report? Maybe a screen shot of said error?
 

Attachments

  • chandoo33868New AF data Feb.xlsm
    118.6 KB · Views: 2
Answer of Question# 3

Right Click on pivot table > click on pivot table option > select Layout & Format and unchecked "Auto fit column width on update"
 
Hi Mate , thanks for your help .

It worked the first time: when I changed from raw data file with 763 records to raw data with 8011

Records , however with 2nd time I press the new raw data button again and select the file again

And error msg comes up as below.

I will attach both files so you can see the difference, raw data file has 8011 records and

Other file I named as raw data mar with 763 records.


I Press the button New Raw Data from PT and it opens a box to select files




Error msg comes



upload_2017-4-16_17-42-8.png



When I press debug it shows this as highlighted
upload_2017-4-16_17-43-43.png

************************************


I changed the Fin_group to Amount_Group , and has just slightly changed the

Vlookup range : =VLOOKUP([@[Amount_Financed]],{0,"0-75k";75000,"75k-150k";150000,"150k-300k";300000,"300k-500k"},2,TRUE)

I have removed the >500k only.

Pivot shows correct ranges however the Amount_Group slicer still shows >500k , couldn’t get

Rid of this even after refresh. So not sure why it appears in slicer when its removed from amount_Group from PT.
 

Attachments

  • chandoo33868New AF data Feb new.xlsm
    609.8 KB · Views: 0
  • Raw data.xlsx
    521.8 KB · Views: 1
  • Raw data mar.xlsx
    59.2 KB · Views: 1
Hi Mate

Please disregard my last msg, as its my bad , I accidently delete something from the pivot table and the macro breaks, it works now and allow me to choose the file and when I refresh pivot it refreshes the data. thanks a lot for your help.

Just few things , I am attaching this new file for you, everything is okay, if you look at the Amount_group slicer , it shows >500k , which shouldn't be a part of it , is there anyway of removing this >500k from slicer setting.

now going forward when I receive new data file, do I have to change its name to
raw data or I can simply select the file by pressing the new raw data button on the pivot table.

Please advise, thanks once again for your help. really appreciate it.
 

Attachments

  • New AF data macro.xlsm
    763.6 KB · Views: 3
No…




the Amount_group slicer , it shows >500k , which shouldn't be a part of it , is there anyway of removing this >500k from slicer setting.
Right-click any cell in the pivot table, choose Pivottable options…, on the Data tab, change the setting of Retain items deleted from the data source | Number of items to retain from field: to None.
Now refresh the pivot table.




now going forward when I receive new data file, do I have to change its name to raw data or I can simply select the file by pressing the new raw data button on the pivot table.
No need to change the file's name. To keep things easy, it should contain a sheet (tab) with the same name every time (Sheet1 ?)
 
Thanks a lot , all fixed now, just for curiosity , if you can shed some light
on this macro Blah , if you like to share some points in this macro as its a quite
a complex macro, it shows active workbook connection as "rawdata1" and source datafile as rawdata2.xlsx, what are the key features of this macro if you would like to elaborate on certain elements of this macro my understanding.

Also if I want to create a similar macro for another file where data set needs to be replaced in a similar fashion then can this macro will be useful or have to make lot of changes in that.

one last question, when you are in the pivot table, under the ribbon tab analyse there is a button called change data source, what is the function of that, can it be used to change the data source to another file.

thanks a lot for your help.
 
As an aside first, you have reduced the number of categories from 5 to 4, with the last one being 300k-500k, but this category includes values greater than 500k, which is misleading; shouldn't it read >300k ?
one last question, when you are in the pivot table, under the ribbon tab analyse there is a button called change data source, what is the function of that, can it be used to change the data source to another file.
Well, it could be if the pivot table's source data was a file (which it can be), but your workbook uses a range in another sheet (in this case now, a table). I stuck with using the table rather than connecting directly to a file because I have no idea what else you might be using the source data for in your project.
Instead of updating the table by cutting and pasting, I updated it by using a query/data connection. So your pivot table and chart get updated by (1) updating the table and (2) refreshing the pivot table.

I set up the query for the table as follows.
On a clean sheet, I went to the Data tab of the ribbon, in the Get External Data section, I clicked on Existing Connections, clicked on Browse for more… at the bottom, in the bottom right dropdown, changed 'All Data Sources' to 'Excel Files', navigated to one of your Raw Data.xlsx files, and clicked Open. In the resulting Select Table dialogue box, chose just the sheet name, made sure there was a tick in the First row of data contains column headers check box, clicked OK, in the next Import Data dialogue box, chose Table, and (this is where you might instead choose to create a pivot table, in which case you will get a new pivot table - you might prefer this), then I accepted the default =$A$1 for where I wanted to put the data and clicked OK.

Now, to get some vba code to change the source file I recorded a macro of my doing the following:
I selected a cell in the Table, went to the Table Tools, Design tab in the ribbon and clicked Properties in the External Table data secion, and in the resulting External data properties dialogue box, clicked on the Connection Properties button:
upload_2017-4-18_12-11-43.png
and in the Connection properties dialogue box, chose the Definition tab and edited the name of the file:
upload_2017-4-18_12-15-49.png
then clicked OK, OK. Then stopped recording.
The only line of importance in the resulting code was
Code:
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Public\Documents\chandoo33868\RawData3.xlsx;Mode=Share Deny", _
        " Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB", _
        ":Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passwor", _
        "d="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OL", _
        "EDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Vali" _
        , "dation=False")
The part to the right of the equals sign is actually an array, and doesn't need to be, I converted it to a single string:
Code:
.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Public\Documents\chandoo33868\RawData3.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
where you can see the filename:
C:\Users\Public\Documents\chandoo33868\RawData3.xlsx
which I replaced with:
" & fn & "
to get:

Code:
        .Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & fn & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
Then I added code to assign something to fn.

Now you can do it yourself!
 
As an aside first, you have reduced the number of categories from 5 to 4, with the last one being 300k-500k, but this category includes values greater than 500k, which is misleading; shouldn't it read >300k ?


thanks a lot my friend for explaining the macro, I will give it a go.

with regards to your first comment:

I have reduced the categories to 4 , you are right
as I only need :
0-75k
75k-150k
150k-300k
300-k-500k

I don't need 500k plus as its not the requirement for us.

so I believe your formula is covering these 04 categories, please advise if I am wrong.

=VLOOKUP([@[Amount_Financed]],{0,"0-75k";75000,"75k-150k";150000,"150k-300k";300000,"300k-500k"},2,TRUE)
 
Hi

Just a question
can we include 0-75k as under 75k instead of 0 as 0-75k
and then
75k as 0-75k
75k-150k as 150k
150-300k as 300k
300-500k as 500k


can we built into vlookup as described above in your formula..
 
Whatever's between double quotes in this formula (in green):
=VLOOKUP([@[Amount_Financed]],{0,"0-75k";75000,"75k-150k";150000,"150k-300k";300000,"300k-500k";500000,">500k"},2,TRUE)
can be pretty much what you want. Then refresh the pivottable.

Again: this last category includes values greater than 500k, which is misleading; shouldn't it read >300k ?
 
Again: this last category includes values greater than 500k, which is misleading; shouldn't it read >300k ?[/quote]


Hi mate, I got your point but if I add the last category to >300 or > 500 then it gives the category which is incorrect, I am attaching this file for you when I add >300.

all I want is only 04 categories, not sure whether I can achieve it with the vlookup or not.

0-75k
75k-150k
150-300k
300k-500k

by adding the >300k and > 500k adds 5 categories in the pivot table , not sure how to only reflect 04 categories in the pivot table.


I tried to add this code to have 4 categories but then last category
shows 7 counts over 300k , out of which 3 are over 500k , so not sure what could be the right mix of formula to just have 04 categories, please advise.

I am attaching this file.
 

Attachments

  • AF Data file trial.xlsm
    764.6 KB · Views: 2
It's simple, decide what you want to do with these values which are actually in your data:
301,401.66
313,381.80
328,655.32
380,381.80
551,218.00
700,000.00
946,020.88
Just tell me which category and that category's title/name for each of the above.
There's nothing wrong with having 5 or more categories, the pivot doesn't have to show them all (that's exactly what pivots are about).
Then I'll guide you for the formula and how to tweak the pivot table.

The important thing is not to mislead people into thinking they're only seeing certain data… when they're not.
 
Back
Top