pivot table

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
ammartahir1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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?
0
ammartahir1978Author Commented:
i am not i want the full number in there respective cells.


for exacmple

                                                    Cost center
Customer name                              LBA

ABC                                                  001076
0
Roy CoxGroup Finance ManagerCommented:
That would need a VLOOKUP formula or similar but you can't really use VLOOKUP with a Pivot Table
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ammartahir1978Author Commented:
Can i not copy past pivot data and then apply vlookup, can u help?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Look at the attached and see if this is what you are trying to achieve.
WMS_Customer_CostCentre.xlsx
1
Roy CoxGroup Finance ManagerCommented:
I knew I had done something like that years but couldn't remember how
0
ammartahir1978Author Commented:
Hi Subodh,

please see attached as the format i want is on sheet2
WMS_Customer_CostCentre.xlsx
0
ammartahir1978Author 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
0
Rob HensonFinance AnalystCommented:
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.
0
Rob HensonFinance AnalystCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
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.
0
ammartahir1978Author Commented:
Always spot on. Excellent!!!!
0
Rob HensonFinance AnalystCommented:
Thanks for the feedback. For clarity on the question thread, which method did you use in the end?
0
ammartahir1978Author Commented:
Hi Used your attached excel sheet.
0
Rob HensonFinance AnalystCommented:
Yes, but that had both methods I had suggested.

1) INDEX function with copied data from  pivot table

2) Formulas alongside the pivot table
0
ammartahir1978Author Commented:
I am using Sheet 2 which is Index function
0
Rob HensonFinance AnalystCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.