• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot table does not give the answer required

T

tel57

New Member
I have a table that has three columns, Col1=customer, col2=product number, col3=customer number. When using a pivot table I get the product number in the x column and the product number in the y row however, the data can only sum the information at the coordinates when I require the customer number to be shown. I have tried looking at vlookup, Index and Match to add the data to the data area of a copied (text) version of the pivot table but so far without success. If anyone has any ideas to solve this I would appreciate your help.
 
@Tel57...


Welcome to Chandoo.org and thanks for posting your question.


Pivot tables are meant for summarizing data only, not showing actual details at point of intersection. To get what you want, you can use INDEX, MACTH formulas. I made an example with data similar to yours. See it here:


http://img.chandoo.org/playground/mimic-pivot-using-formulas.xlsx


examine the array formula in the output section to understand how this works.
 
Thanks for the help. I have looked at the formula and it works if I copy and paste directly but when I make amendments to the formula to adjust for data range it either shows nothing in the cells (the brackets {} disappear when I make the changes) or when I add the {}brackets back in the cells just show the formula. Any further help would be welcome.
 
Hi tel57,

Then you need to press CTRL+SHIFT+ENTER.

Please locate your cursor on formula and press F2 (in editing mode) then press CTRL+SHIFT+ENTER


Hope this helps!


Thanks

Ariff
 
You must log in or register to reply here.
Back
Top