Question How to get results from 3 different columns in an Excel Pivot Table ?

[UknownPavlos]

Hello , i hope i can explain what i need the best i can. I have an Excel Pivot Table which is arranged like so:

_COMPANY | _CODE | QUANTITY
COMP. 1 | POS | 1
COMP. 1 | PDA | 2
COMP. 2 | POS | 2
COMP. 3 | POS | 1
COMP. 3 | PDA | 2
COMP. 4 | PDA | 1

I need a counter for the _COMPANY which is gonna count the total of companies which has all these diferent combinations. Example ->
(i need all different compinations be generated based on the range of quantity.)

POS |PDA |COUNTER RESULT
1 |0 |0 (COMPANIES)
0 |1 |1 (COMP.4)
1 |1 |0 (COMPANIES)
1 |2 |2 (COMP.1 & COMP.3)
2 |0 |1 (COMP.2)
2 |1 |0 (COMPANIES)
2 |2 |0 (COMPANIES)

Is it even possible to do that in Excel ?

```

_code	(Multiple Items)
_code	(Multiple Items)
Count of infoname	Column Labels
Row Labels	0​	1​	2​	3​	4​	5​	6​	7​	8​	12​	Grand Total
0	3​	3​	3​	3​	3​	3​	3​	3​	3​	3​	3​
1	235​	235​	235​	235​	235​	235​	235​	235​	235​	235​	235​
2	170​	170​	170​	170​	170​	170​	170​	170​	170​	170​	170​
3	43​	43​	43​	43​	43​	43​	43​	43​	43​	43​	43​
4	26​	26​	26​	26​	26​	26​	26​	26​	26​	26​	26​
5	7​	7​	7​	7​	7​	7​	7​	7​	7​	7​	7​
6	4​	4​	4​	4​	4​	4​	4​	4​	4​	4​	4​
7	1​	1​	1​	1​	1​	1​	1​	1​	1​	1​	1​
8	3​	3​	3​	3​	3​	3​	3​	3​	3​	3​	3​
12	1​	1​	1​	1​	1​	1​	1​	1​	1​	1​	1​
Grand Total	493​	493​	493​	493​	493​	493​	493​	493​	493​	493​	493​

```
I hope this helps to understand what I need, but it has wrong results.

 

[Ralston18]

Grand totals in the bottom row are summing correctly.

Wrong results being:

The values in each row/column cell are incorrect? What/how is the value in the row/column cells being determined?

Grand totals in the far right column (not summing horizonally) - should sum horizonally - correct?

Add an example showing (even if done manully) what the expected results should be.

 

[UknownPavlos]

Grand totals in the bottom row are summing correctly.

Wrong results being:

The values in each row/column cell are incorrect? What/how is the value in the row/column cells being determined?

Grand totals in the far right column (not summing horizonally) - should sum horizonally - correct?

Add an example showing (even if done manully) what the expected results should be.

Yes you are right , let me explain.
By saying i am getting wrong results , i don't mean the totals, for example in the excel i got.
I have 5 companies who have same quantities of POS and PDA , so that mean that i want a result (for example)
3 PDA | 5 POS | 5 COMPANIES
all of them has 3 pda and 5 pos , if a company has 2 pda kai 5 pos , it won't be counted on that combination, it will create a new one which is gonna be
2 PDA | 5 POS | 1 COMPANY ( it is only one company because in the rest of the excel there is no other company who has 2 pda and 5 pos) is this explanation more clear ?

 

[Ralston18]

So if Company X has 4 PDA and 5 POS

and Company Y has 4 PDA and 5 POS

and Company Z has 4 PDA and 5 POS

With only PDA and POS counts being collect for each company?

The required end result would be 4 PDA | 5 POS | COMPANY X & COMPANY Y & COMPANY Z

What data goes in the Pivot table Row and Column lables?

= = = =

Overall, the requirement is more likely suited to a database.

However, will stick with Excel for the moment. Excel is very powerful and with some forethought into how the data is captured and presented such a report may be readily created.

Are you able to provide a couple of screenshots showing the necessary data from all spreadsheets and the "reporting" spreadsheet showing the counts?

Or just do a more detailed "mock up" of the data source and required report. No formulas per se. Just show how the data (PDA, POS, Company Name) is entered and the corresponding cell entries in the rows and columns being reported.

Especially with the far left row column and column headers Along with the expected Grand Totals.

Two things:

For discussion purposes use uppercase/capital letters for company names and not a "number".

Likewise provide a larger example of the datasource with clearly labeled rows and columns.

Edit to add: Also look into Excel Crosstab.

 

[UknownPavlos]

So if Company X has 4 PDA and 5 POS

and Company Y has 4 PDA and 5 POS

and Company Z has 4 PDA and 5 POS

With only PDA and POS counts being collect for each company?

The required end result would be 4 PDA | 5 POS | COMPANY X & COMPANY Y & COMPANY Z

What data goes in the Pivot table Row and Column lables?

= = = =

Overall, the requirement is more likely suited to a database.

However, will stick with Excel for the moment. Excel is very powerful and with some forethought into how the data is captured and presented such a report may be readily created.

Are you able to provide a couple of screenshots showing the necessary data from all spreadsheets and the "reporting" spreadsheet showing the counts?

Or just do a more detailed "mock up" of the data source and required report. No formulas per se. Just show how the data (PDA, POS, Company Name) is entered and the corresponding cell entries in the rows and columns being reported.

Especially with the far left row column and column headers Along with the expected Grand Totals.

Two things:

For discussion purposes use uppercase/capital letters for company names and not a "number".

Likewise provide a larger example of the datasource with clearly labeled rows and columns.

Well i did figure out what to do , it's a little bit complicated to be done by someone who doesn't have any idea of excel. I was hoping it would be easier. Let me explain.
```

_company	infoname		_code	quantity
010563​	COMPANY X		EDS	1​
010563​	COMPANY X		MGR	1​
010563​	COMPANY X		DAT	1​
010563​	COMPANY X		STD	1​
010563​	COMPANY X		POS	3​
010563​	COMPANY X		PDA	1​
010563​	COMPANY X		MSC	1​
009328​	COMPANY Y		STD	1​
009328​	COMPANY Y		POS	1​
009328​	COMPANY Y		PDA	2​
010849​	COMPANY Z		STD	1​
010849​	COMPANY Z		PDA	1​
010849​	COMPANY Z		DAT	1​
010849​	COMPANY Z		BRC	1​
010849​	COMPANY Z		EDS	1​
005690​	COMPANY R		PDA	1​
005690​	COMPANY R		EDS	1​
005690​	COMPANY R		DAT	1​
005690​	COMPANY R		MGR	1​
005690​	COMPANY R		BRC	1​
005690​	COMPANY R		STD	1​
005690​	COMPANY R		POS	4​
005690​	COMPANY R		CRM	1​
005690​	COMPANY R		BAS	1​

```
I made a pivot table as shown in images.

https://ibb.co/D1W40vM

https://ibb.co/Xs83s90

Based on the pivot table i made , i added a function which gets the "column name" and shows it on V column, one function for PDA and one for the POS. Then i had to combine them and get the results i need. On the first line you'll see 2+1 (which means 2 PDA and 1 POS). Then i murged and categorised the combinations column and i added counter for companies. In another pivot table and got the result i need.

https://ibb.co/jWk8cSS

Thank you for your time , if you have a faster way on your mind , please share it to me

 

[Ralston18]

Do the functions etc. correctly report the count of companies with combination null+null?

I did not note that count being shown. Unless null+null does not matter.

Not really sure about "a faster way"......

My suggestion is to continue to use what you are working with and have the certainty that the combination counts are correct.

Then establish a corresponding test spreadsheet where you can use the same data and look for ways to make quantifiable improvements with regards to performance, simplicity, ease of use, reported results, etc..

My overall belief being that (as circumstances, data. and reporting requirements change) you will learn more and will be able to make the spreadsheet and counts all work as required.

However to avoid getting into what may well become a very cumbersome report do some reading about database design.

Very positive, in your favor, that you can work with functions.

And I still think that Crosstabs may fit in as well.

 

[UknownPavlos]

Do the functions etc. correctly report the count of companies with combination null+null?

I did not note that count being shown. Unless null+null does not matter.

Not really sure about "a faster way"......

My suggestion is to continue to use what you are working with and have the certainty that the combination counts are correct.

Then establish a corresponding test spreadsheet where you can use the same data and look for ways to make quantifiable improvements with regards to performance, simplicity, ease of use, reported results, etc..

My overall belief being that (as circumstances, data. and reporting requirements change) you will learn more and will be able to make the spreadsheet and counts all work as required.

However to avoid getting into what may well become a very cumbersome report do some reading about database design.

Very positive, in your favor, that you can work with functions.

And I still think that Crosstabs may fit in as well.

In my function basicly was trying to get the name of the column based on where the 1 was. if there were no 1's in that line , i was getting error , that's why i had to add an if function to have some results if the function had no results, which was returning nulls, but now i changed it into 0's to get more clear results.

I will check about Crosstabs , thank you so much for your time.