Make a chart/pivot chart for an inventory

[alisav]

Hello!

I am trying to make a (pivot) chart for my inventory. I have about 500 inventory items that I have to count every three months. From this data I need a chart in which I can see evolutions and I would also need to be able to compare quarters and years with each other. For example if I choose one inventory number then I would want to be able to see how many we had in stock last year compared to this year.

My question is: is it possible to create something like this? If yes, how do I do it?
I've added an example in the attachments.

Thanks in advance!

[sas5610]

Yes, You can easily achieve that through Pivot table but your data is not in a proper format. You need to fix your data structure first by:
1-Converting your text dates to date format
2-Converting data to table from data tab, get data and transform section
3-Select all items columns and select unpivot column
4-Rename values and attribute as items
5-then create a pivot table to do the comparisons.

I have done all above for you. Attached the revised sheet.
Hope it works for you.

[dflak]

I started out like Sas did, but decided that you probably wanted something reproducible. Getting the data into normalized format is the right way to go. So if you can rearrange the data collection to continue to collect the data, then go with that solution. My solution assumes that the data is coming to you in a report in the format you provided. If that's the case, clear out the table and copy and paste the new data in.

First I made a table out of the data. Tables know how big they are so when you add items or dates, you don't have to change the formulas.

I have two named ranges:
List_Dates =Table1[#Headers]
List_Inventory =Table1[Inv. N°]


I use these to provide the dropdowns in cells B1, B3 and B4.

Then I use the match command in Cell D1 to find the row on which the inventory item occurs.

I also use match in Cells D3 & D4 to find the column where the selected dates occur.

Then the little table in cells B7:D8 uses Index to find the values.