Pivot to show top 5, However.....

[nparsons75]

I have a normal table of data, in most cases the data is vertical, however, within the table is some data that is horizontal, by this I mean running along the columns as opposed to in rows. I have managed to create a pivot table by adding calculated fields of the same data in the columns.

Although I have managed this I cannot sort the data A-Z.

I need to find the top 5 items from the data.

Is this possible at all?

Thanks in advance....

 

[RoryA]

You should be able to filter a row field by the Top 5 of a given value field.

 

[nparsons75]

Hi, It wont let me. I have a pivot table built from calculated fields, however it will not let me filter as it will with a normal pivot table?

 

[RoryA]

What exactly do you mean by "won't let me"? Even a Mac can do that.

 

[nparsons75]

its very hard to explain...

My data in the main table is mixed, some data is vertical, some horizontal. I need to pivot the horizontal data which is not always possible, pivot data should be vertical i believe as a pivot table assumes the first row as the header. Along each row are headers. The data i need, for example, is like this...


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]ID 1[/TD]
[TD="align: center"]ID 2[/TD]
[TD="align: center"]ID 3[/TD]
[TD="align: center"]ID 4[/TD]
[TD="align: center"]ID 5[/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]122[/TD]
[/TR]
</tbody>[/TABLE]


From this, I need to understand the top 3 items and list them. For example,

ID2 150
ID5 122
ID1 55


As i filter the data table this will change...

The only way i can think is using the pivot, however, I cannot transpose the data vertically as its part of a bigger table...

 

[RoryA]

That really doesn't sound suitable for a pivot table unless you can clean up the data first. Formulas would probably be a better bet, though if you have Power Query/Get and Transform, you should be able to clean it up pretty easily.

 

[nparsons75]

Hi Rory, thanks for the help, however, I havent got power query or even know what it is...

 

[Joyner]

Hi Rory, thanks for the help, however, I havent got power query or even know what it is...

What version of Office / Excel do you have?

 

[nparsons75]

Hi,

Office home business 2016

 

[RoryA]

Do you not have a Get And Transform section on the Data tab?