A -possibly- easy issue I'm having trouble with - filtering, extracting data

[dourpil]

Hi all, I hope you're doing great this fine day.

I'm trying to do the following thing but don't really know where to start:
So here is what my sheet looks like:
(A)(B)(C)
X | 1 | 4
Y | 2 | 6
X | 2 | 1
Y | 5 | 6
Y | 7 | 8
X | 4 | 2
I would like to "sort" that data and create this kind of table:
for X
1 | 4
2 | 1
4 | 2
for Y
2 | 6
5 | 6
7 | 8
So it seems I have to play with filters (from what I've seen) but I don't want to have a range of data selected because I want to be able to add data in my first table and have it automatically copied in the corresponding sorted table.
So I don't really want to filter my table but create another one with only the information I need. And I can "only copy filtered data to the active sheet" when I try to use the filter thingy.

I also looked up (pun incoming) stuff about the vlookup (yeey!) function but it doesn't seem to be what I'm looking for.

Any tip - link - stuff - cake you can give me?

Thanks in advance!
Simon

 

[Snakehips]

Q1 - You say you have changed the 'filtering thingies' fro X & Y to numbers. Is that just two numbers one for X and one for Y ???

Q2 - 'Filtering thingies' aside, is the result of my formula typical of what you want???

Q3 - Have you tried giving your Excel some ";" instead of "," ???? Do you have regional setting that requires ; rather than , ?????

 

[dourpil]

Hi,

Sorry its difficult to tell from the information you've posted what the problem is with the PivotTable on your actual data.
As long as the actual data is similar to the sample posted the setup should be the same.

Perhaps post a more representative sample of your actual data using the method described here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

Or as a last resort if you also want to include your PivotTable setup, post an anonymised workbook on Skydrive for example.

Indeed! I realized my query was quite unclear and difficult to adress, my apologies.

Anyway I finally decided to start from scratch so I can understand better how PivotTables work.

Here is where I'm at right now: (you can see more clearly what I wanted to do): http://sdrv.ms/1biGvrK

I have a few more questions if I may.. If I'm being unclear or you still feel I'm posting "the wrong way", don't hesitate to let me know! I deeply appreciate the help I'm getting but I'm aware I don't use the language very well.

1- As you can see, the data is sorting in an odd fashion: the order isn't correct (although in the options > Display > Field list, the "in data source order" is checked)
2- The dynamic table is great, I just understood how the table grew as entries were made. I'd like to push that if possible. My column C called 'Substance' has the same 3 names that are always the same. Is there a way that these names 'dynamically' update as data is entered in new rows? Instead of having to select the 3 rows and click+drag the bottom right corner.
3- My goal will eventually be to have as many tables as I have tanks. I discovered the "Options > Show report filters pages..." which looks like a great way of doing that (instead of creating a pivottable for each tank value) but now my data isn't sorting very well so that's not working atm. If there's a better way of doing that, I'm all ears!


Thank you!

 

[dourpil]

Q1 - You say you have changed the 'filtering thingies' fro X & Y to numbers. Is that just two numbers one for X and one for Y ???

Q2 - 'Filtering thingies' aside, is the result of my formula typical of what you want???

Q3 - Have you tried giving your Excel some ";" instead of "," ???? Do you have regional setting that requires ; rather than , ?????

Hi and sorry to you too for being unclear.

Q1: technically I have more than two numbers. I have from 13 to 20 (approx). When I change the "X" by "13", nothing is displayed but no error message either. When I change "X" by just 13, I have all zeros. I can perhaps send you my file if you want to take a look at it but I think I'll be using pivottables though..

Q2: yes indeed

Q3: I did indeed. It works that way. That was dumb of me, sorry.

 

[circledchicken]

I have a few more questions if I may.. If I'm being unclear or you still feel I'm posting "the wrong way", don't hesitate to let me know! I deeply appreciate the help I'm getting but I'm aware I don't use the language very well.

1- As you can see, the data is sorting in an odd fashion: the order isn't correct (although in the options > Display > Field list, the "in data source order" is checked)

Hi,

I think its still a bit unclear. The data looks sorted in ascending order to me?
What is the output you were expecting?

 

[dourpil]

Hi,

I think its still a bit unclear. The data looks sorted in ascending order to me?
What is the output you were expecting?

In the sheet "TANK encodage", the data corresponding to "tank n° 13" is entered in that order:

0 3
0 1
14 35
0 0

But in "TANK tables", the order is:

0 1
0 3
0 0
14 35

Which is not the order corresponding to the data source order.. Is it clearer?

 

[circledchicken]

Yes, its clearer.

The 'in Data Source Order' option you are referring to does not actually affect the PivotTable, its just the displayed field list in the PivotTable Fields dialog that is sorted for convenience.

To sort in the way you want, one way you can try is to add an extra column to your 'TANK encodage' table (e.g. call it 'Sort Order'), and enter the position number for each row e.g. just 1,2,3,4,5 etc.

Or use this formula in the Sort Order column to have it populate automatically:
=ROWS(Table1[[#Headers],[Sort Order]]:[@[Sort Order]]) - 1

Then, just refresh your PivotTable and add the new 'Sort Order' field to the Row Labels (as the first Row Label, above the CC field).

 

[circledchicken]

Also, regarding your question of having the 'Substance' names dynamically generated, try this formula in the 'Substance' column (assuming you have your Sort Order column set up):

=CHOOSE(
    MOD([@[Sort Order]] - 1, 3) + 1,
    "Sucre", "Acide aminé", "Emulsion")

 

[circledchicken]

Here is a sample file if it helps:
http://sdrv.ms/17WaKXc

 

[dourpil]

Here is a sample file if it helps:
http://sdrv.ms/17WaKXc

This is great, thanks a lot!

I can work with that file the way it is now. However if you have the courage to keep enduring my basics queries, I would like to go further.

- As I mentioned earlier, ultimately I want all my "Tanks" to have their stats displayed at once. I know there's the "show report filter pages.." that can help me do that and automatically create the tables for me when I have data for each tank that'll be on my table. This is acceptable for me.

- I will want to have charts to view my data in a fashionly manner I can do it manually as shown in this example: http://sdrv.ms/1biGvrK
but it's not practical as i'll have to adjust the ranges.. I tried to create a pivottchart but don't know how/where to place the stuff..

 

[circledchicken]

- I will want to have charts to view my data in a fashionly manner I can do it manually as shown in this example: http://sdrv.ms/1biGvrK
but it's not practical as i'll have to adjust the ranges.. I tried to create a pivottchart but don't know how/where to place the stuff..

I think it can be a bit tricky working with Pivot charts and some of the functionality might be limited, but in any case I can't see any charts in your example.