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

How to replicate a pivot table query?

S

Supat26

New Member
Hi,
I have a HUGE data that I am summarizing in a report, and want to show the maximum sales number iif certain conditions are met. I can easily do it with a pivot table, and then linking the result to the report. However, I am trying to figure if there is a formula or code that can be used that would eliminate the use of pivot table? Here is an example of the dataset: The answer I am looking for is just 265$. Thanks much in advance for any hellp!

Name sales $ Sector issue
jill 100 4 private
jack 80 5 public
joey 40 5 private
josh 60 4 private
jill 150 3 public
jack 160 4 public
joey 225 5 private
josh 150 5 private

Here is the pivot table result.

issue private
Sector 5

Sum of sales $
Name Total
joey 265
josh 150
Grand Total 415
 
Hi ,

It is certainly possible using formulae ; can you please upload your file with some sample data ?

Posting the data here means someone has to copy paste this data into a blank worksheet , do a Text to Columns to separate the data into different columns , and then develop the formula.

If you can upload your file , it will save others some effort.

Narayan
 
Thank you. I probably did not post my question correctly. Basically I'm filling out a questionnaire in 1 tab and the questionnaire has questions like what is the largest single sector, single private, sales amount. The other tab has 20,000 rows for example. So I can I get to the same result by doing a unique list on the sales people and then doing sumifs against them. What I'm trying to do instead is something like a sum product Array formula which will look at the entire dataset, and do the same thing that a pivot table query would result in. I need the $265 for answer this month, but every month that number would change, and I do not want to create unique name list each month, or increase the size of the file by creating pivot tables. Hope this isn't confusing :|
 
Hi ,

I do not know in what way the formulae are not doing what you want them to do. Let me explain.

1. We need to generate a unique distinct list of names which fulfill certain criteria ; initially you mentioned 2 criteria , now you have added a third.

2. Once we have the names , the same criteria which were used to generate the list of names , can be used within a SUMIFS function to total the sales.

3. The criteria you initially mentioned were the SECTOR and the ISSUE ; I have made use of the values in H1 and H2 so that if you change these values , your pivot table will change , and at the same time , the formulae will take the same values to calculate their results ; you can then compare the results the formulae give versus the results from the pivot table.

4. If you now add a third criterion viz. the month , add one more cell where the report month can be specified , and include the month in both formulae viz. the formula which is being used to generate a list of unique names , and the SUMIFS formula.

If you have any difference of opinion on the above , please let me know exactly where you differ.

Narayan
 
I didn't mean to say that your solution won't work. It definitely works, and I already knew how to do it. I am trying to achieve the same result, without having to create a unique name list, and doing a sumifs against names - and then finally doing a max on all those sumifs.
 
Hi ,

If you do not want formulae to generate a unique list of names , and then do a SUMIFS against those names , there are exactly 2 options available :

1. Use pivot tables

2. Use VBA

If you have come across any other way , please let me know.

Just BTW , all of this was not mentioned in your initial post , which said :
However, I am trying to figure if there is a formula or code that can be used that would eliminate the use of pivot table?
Click to expand...
Also , I do not understand where the MAX function figures in all this.

Narayan
 
In my example worksheet, the two answers would be joey = 265, and Josh =150. To answer my questionnaire, for the largest amount of sales, I would need max of the two. My real data would have about 5000 unique names, so I would need to use the max function.
 
Hi ,

Since your data has names which may or may be fulfill the 3 criteria of Sector , Issue and Month , I am not sure that one formula would be able to extract the list of names and retrieve the sales figures for each name and total them up and take the maximum value.

I would not even trouble myself to develop such a formula when there are much simpler ways of doing the same thing.

The first reason would be why ?

You can always wait for the person who might ask Why not , and come up with the formula.

Narayan
 
My file already is huge, plus the summary worksheet I am trying to answer has 20 such questions, each one with a unique set of parameters. I am currently using pivot tables which makes the file size even larger. I was trying to eliminate the extra steps of creating unique lists, for every single question on my questionnaire. Sounds like I belong to the 'why not' group :) For now, I created an extra column and will run a MAX function on this column. See attached. Thanks again for the discussion!
 

Attachments

  • ch_test.xlsx
    13.6 KB · Views: 4
Hi ,

A huge file size is , by itself , not an inconvenience ; slowness of response is more of an inconvenience.

If you can get a result using a pivot table , it will always result in a faster response , than if you were to get the same result using formulae. Using array formulae always results in increased calculation times , unless the array formula is a simple one and used over multiple cells , rather than a single cell array formula which is copied over many rows , which is why using helper columns can result in an improvement. Using VBA is an option , if you are willing.

Narayan
 
Hi ,

Will the criteria be just the 3 that you have mentioned viz. Sector equal to the given sector , Issue equal to the given issue , and Month equal to the given month ?

Can you upload a sample file which includes the month column ?

Narayan
 
Hi Supat ,

See this file.

I have not completed it , but you can use it under the following conditions :

1. Use the pivot table filter fields to select the criteria.

2. Either select single criteria in the fields or select (All) ; do not select multiple items , since I have not yet coded for it.

Narayan
 

Attachments

  • ch_test.xlsm
    22.5 KB · Views: 5
Hi Supat ,

No. The pivot table is not necessary except for you to verify , if you wish to do so.

The only requirement is that the code uses cells H1 and H2 for the selected criteria , H1 for the selected sector , and H2 for the selected issue.

If you remove the pivot table , and you wish to use other cells for this purpose , change the code accordingly.

Narayan
 
Got it. It works great. I will refer to the "
HELP! Where do I put the code"
section to understand how to import the code into my working file. Thanks again for being so helpful!
 
For the benefit of other readers, I am posting the response I sent to Supat based on his private query to me...
-------------
Hello Supat,
It is best to ask questions in the general forum since I don't always get time to check this forum in a timely manner.

Here is something quick and dirty that finds the name of the person with the highest sales amount, and meets the criteria for issue and sector:
=INDEX(NameList, MATCH(1, FREQUENCY(-99999, -IF((IssueList=Issue2Find)*(SectorList=Sector2Find)*FREQUENCY(MATCH(NameList,NameList,0), MATCH(NameList,NameList,0)), SUMIF(NameList,NameList,Sales))), 0))

enter with Ctrl + Shift + Enter

To get the sales total for the above person, use the following formula:
=SUMIF(NameList,L6,Sales)

Attached is a file demonstrating the above formulas.

I am sure the formulas can be optimized, but I will leave that as an exercise for you!

I was not sure why you had shown two names in your example above: joey and josh, since I was not sure what the criteria was for josh.

From your posts, I did not understand whether you have more criteria to include (i.e. beyond Issue and Sector). If you do, extend the section in red in the above formula, by multiplying additional conditions.

99999 is meant to be a large number. If your sales amount totals can be larger than that, please change it to a suitably large number.

Cheers,
Sajan.
 

Attachments

  • Chandoo-Supat-ch_test.xlsx
    12.9 KB · Views: 4
Hello,
To handle cases where the criteria for Issue and Sector are not met, use a formula like the one below:
=LOOKUP(SUMPRODUCT((IssueList=Issue2Find)*(SectorList=Sector2Find)), 1/FREQUENCY(-99999, -SUMIFS(Sales, IssueList,Issue2Find,SectorList,Sector2Find, NameList, NameList)), NameList)

It returns an error when the criteria is not met. Feel free to trap it using IFERROR if desired.

-Sajan.
 

Attachments

  • Chandoo-Supat-ch_test.xlsx
    12.8 KB · Views: 3
You must log in or register to reply here.
Back
Top