pivot table

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?

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


for exacmple

                                                    Cost center
Customer name                              LBA

ABC                                                  001076

That would need a VLOOKUP formula or similar but you can't really use VLOOKUP with a Pivot Table

Can i not copy past pivot data and then apply vlookup, can u help?

Look at the attached and see if this is what you are trying to achieve.
WMS_Customer_CostCentre.xlsx

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

Hi Subodh,

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

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

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.

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.

Always spot on. Excellent!!!!

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

Hi Used your attached excel sheet.

Yes, but that had both methods I had suggested.

1) INDEX function with copied data from  pivot table

2) Formulas alongside the pivot table

I am using Sheet 2 which is Index function