pivot table

ammartahir1978
ammartahir1978 used Ask the Experts™
on
hi All,

I have a very simple request, i dont know why i can not sort this out myself.

I have a pivot table which have two columns Custno(Column) and Costcent (Row), now i want customer_code which is CUSTNO to be displayed in there respective cells, which i put it in it shows only 1, i want the whole number.

can someone help please.
WMS_Customer_CostCentre.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
You are getting a 0 because the Customer Numbers are Text and you are trying to sum them. Why would you want to sum the cust number?

Author

Commented:
i am not i want the full number in there respective cells.


for exacmple

                                                    Cost center
Customer name                              LBA

ABC                                                  001076
Roy CoxGroup Finance Manager

Commented:
That would need a VLOOKUP formula or similar but you can't really use VLOOKUP with a Pivot Table
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Can i not copy past pivot data and then apply vlookup, can u help?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Look at the attached and see if this is what you are trying to achieve.
WMS_Customer_CostCentre.xlsx
Roy CoxGroup Finance Manager

Commented:
I knew I had done something like that years but couldn't remember how

Author

Commented:
Hi Subodh,

please see attached as the format i want is on sheet2
WMS_Customer_CostCentre.xlsx

Author

Commented:
appreciate all your help, thank you.

i want the format which is in my sheet customers in column and costcenters in row and the results in there respective cells please.

Thank you
Rob HensonFinance Analyst

Commented:
With a helper column you can use INDEX rather than Pivot Table.

In column F of sheet1 use this formula, starting in F2 and copied down:
=D2&"_"&E2

This concatenates the Customer Name and Cost Centre into a unique value

Then on sheet2, starting in cell D4 and copied down and across use:
=IFERROR(INDEX(Sheet1!$B:$B,MATCH($C4&"_"&D$3,Sheet1!$F:$F,0)),"")

Note the use of $ to lock the Customer Name column and the Cost Centre row.
Finance Analyst
Commented:
Take a look at the attached. Still uses Pivot Table but with formulas alongside.

The Pivot creates a grid of Customer and Cost Centre with Customer Number as a row field and a value field as a Count. The value fields are then hidden by Grouping.

Columns AF to AX are linked to the Columns of the Pivot Table and the following formula in rows below:
=IF(J6="","",$I6)

Column J is the first of the Value fields and column I contains the Customer Number. So, if Value field is blank result is blank else result is Customer Number.

Sheet2 also shows the earlier INDEX suggestion.

Downsides of each:
INDEX won't be updated automatically for new customer/cost centre combinations; manually copy and paste and extend formulas.

When pivot expands for new customer/cost centre combinations the formulas in columns AF onward could get overwritten.
WMS_Customer_CostCentre--1-.xlsx
Most Valuable Expert 2011
Top Expert 2011

Commented:
Which version of Excel do  you have? If you have the data model and Power Pivot, you can use CONCATENATEX in a measure to return text values in the value area of the pivot table.

Author

Commented:
Always spot on. Excellent!!!!
Rob HensonFinance Analyst

Commented:
Thanks for the feedback. For clarity on the question thread, which method did you use in the end?

Author

Commented:
Hi Used your attached excel sheet.
Rob HensonFinance Analyst

Commented:
Yes, but that had both methods I had suggested.

1) INDEX function with copied data from  pivot table

2) Formulas alongside the pivot table

Author

Commented:
I am using Sheet 2 which is Index function
Rob HensonFinance Analyst
Commented:
I thought you might, that is less risky of overwriting than the formulas alongside the pivot.

For updating, are you aware of the Paste Special - Skip Blanks option?

Change the pivot table back to just Customer Name in the rows fields and Cost Centre in the column headers with nothing in the value field; you then have just a column of customer names and a row of cost centres to produce your grid. You can then select the Customer Name column and across the grid for the Cost Centres. Copy this to the clipboard and then go to sheet 2 for pasting. Select the top left of the existing grid and go to the Paste Special menu. Select "Values" option at the top section and at the bottom select the "Skip Blanks" option ans click OK. This will then only paste the Customer Names and Cost Centres as the rest of the grid is blank and won't overwrite the INDEX formulas in the grid. You will then just need to check that all rows and columns of the grid are filled with the INDEX formula; if there are more customers or cost centres they will need filling.

Alternatively, you could just use the Pivot to create the first grid and then have a check formula in the source data to make sure that the Customer Name and Cost Centre are included in the grid. If check shows that they are not then add them manually.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial