Hi Myanda,

Thanks you, we are almost done 🙂
now, how can we display the result by line without duplicate like in the pivot, I mean for 1 object, the number of item and the sum to avoid the repetitive values.
You see in the file I have attached, the result in each column appears at each iteration.

thanks
Stephanie

Hi Stephanie,

You have to create a summary of the data in a separate table (i.e. where the object number is not repeated), which is another reason why the PivotTable is the best option.

You can do this manually with a UNIQUE formula ( https://www.myonlinetraininghub.com/excel-functions/excel-unique-function), but then you also need a dynamic named range ( https://www.myonlinetraininghub.com/excel-dynamic-named-ranges) for the chart to reference the data table, so that as the data grows/shrinks, the chart automatically adjusts.

Have a go yourself and if you get stuck, share your file so we can see where you've gone wrong...assuming there's a very good reason why you won't use a PivotTable for this.

Mynda

Thanks Mynda

is there a course you did with the demo in your excel Expert program to use and try to replicate, that would be very hekpful.

Stephanie

Hi Stephanie,

The UNIQUE function (to generate the distinct list of objects) is covered in lesson 3.62 and dynamic named ranges are covered in 3.75.

There is no lesson that covers manually creating a PivotTable using formulas (which is essentially what you need), because I would never recommend that approach. I'm still not understanding why you can't use the PivotTable with a Pivot Chart?

Mynda

Hi Myanda,

i tried my best to combine everything in the formula but i did not succeed to make it works, unless you give me a final hand, i will give up 🙁
To answer your question, it is just a personal challenge to do it out of the pivot

Thanks
Stephanie

Hi Stephanie,

It's great to see you're trying different things. When you make attempts and get stuck, it's best if you can share your file with your attempts so we can see where you went wrong and help you from there, otherwise we are just doing the work for you, and you don't learn as effectively.

See 'Formulas' sheet in the file attached.

Mynda

Many Thanks Mynda !