How to extract data from three distinct columns in an Excel Pivot Table?
How to extract data from three distinct columns in an Excel Pivot Table?
Hello, I believe I can clarify exactly what you require. I possess an Excel Pivot Table structured in a specific way, like this:
_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
Your goal is to create a counter that tallies the total number of companies with all these unique combinations. For example:
(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)
It seems possible to achieve this in Excel?
The overall sums at the bottom are accurate.
Incorrect outputs appear to stem from: Are the numbers in each row/column cell being calculated correctly? How do these values get assigned?
The grand totals in the far right column (not meant to add up side by side) should instead total together—would you like an illustration of what the proper results would look like?
By mentioning I'm getting incorrect results, I don't mean the overall totals, such as those in the Excel file. I have five companies with identical amounts of POS and PDA, so I need a specific output (for instance):
3 PDA | 5 POS | 5 COMPANIES
where each company has 3 PDA and 5 POS. If a company has 2 PDA and 5 POS, it won't be included in that group, and a new combination will be formed:
2 PDA | 5 POS | 1 COMPANY (since there are no other companies with 2 PDA and 5 POS in the rest of the Excel). This explanation is clearer?
So if Company X possesses 4 pda and 5 pos, Company Y also has 4 pda and 5 pos, and Company Z has 4 pda and 5 pos, focusing only on the counts of pda and pos for each company. The expected output would be 4 pda | 5 pos | COMPANY X & COMPANY Y & COMPANY Z.
The data placed in the pivot table row and column labels should reflect the company names clearly.
Overall, this setup seems more aligned with a database solution, but for now we’ll keep using Excel. It’s quite powerful, and with careful planning on how the information is gathered and shown, such a report can be made quickly.
Would you like me to provide a few screenshots of the necessary spreadsheets and the reporting sheet? Or would you prefer a more detailed mock-up of the data source and the required report?
Also, consider using Excel Crosstab for better visualization.
Do the functions etc. accurately reflect the number of companies with combination null+null? I didn't consider showing that count unless it's significant. Unless null+null is irrelevant.
I'm not sure about a quicker method...
My advice is to stick with what you're using and be confident the counts are accurate.
Then create a matching test spreadsheet where you can apply the same data and explore improvements in performance, simplicity, usability, and reporting results.
I believe that over time (as circumstances, data, and reporting needs evolve) you'll gain more knowledge and be able to handle spreadsheets and counts effectively.
But to prevent turning into a very complicated report, I recommend some research on database design.
Very encouraging that you can work with functions.
And I still think Crosstabs could also be useful.
In my function I was mainly attempting to retrieve the column name based on the presence of a '1'. If there were no '1's in that line, an error would occur, which is why I added an if condition to provide some results even when there were none, resulting in nulls. Now I have switched it to using 0's for clearer outcomes.
I will review the Crosstabs and thank you very much for your time.