SOLVED

# Sorted Ranked Data on separate sheet

Highlighted
Occasional Contributor

# Sorted Ranked Data on separate sheet

How can I get the ranked data on a separate sheet that is sorted with formula?

Two sheets I have, data and summary. The data sheet has items ranked. There are 3 columns that I want to show in the next summary sheet that is in ascending order.

See attached file.

Thanks,

Jay.

3 Replies
Highlighted
Solution

# Re: Sorted Ranked Data on separate sheet

I probably would do it without formulas.

`=INDEX(Data!\$A\$2:\$A\$6,MATCH(B2,Data!\$B\$2:\$B\$6,0))=SMALL(Data!\$B\$2:\$B\$6,ROWS(B\$2:B2))=INDEX(Data!\$C\$2:\$C\$6,MATCH(B2,Data!\$B\$2:\$B\$6,0))`

Highlighted

# Re: Sorted Ranked Data on separate sheet

@Detlef Lewin  Thanks that works. I would agree that VBA code may have been a better approach but that is beyond my scope and I am happy with this.

# Re: Sorted Ranked Data on separate sheet

I wasn't thinking of VBA but of PQ.

`letSource = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Overall Rank", Int64.Type}, {"Average", type number}, {"Rank1", Int64.Type}, {"Rank2", Int64.Type}, {"Rank3", Int64.Type}, {"Rank4", Int64.Type}, {"Rank5", Int64.Type}, {"Rank6", Int64.Type}, {"Rank7", Int64.Type}, {"Rank8", Int64.Type}, {"Rank9", Int64.Type}, {"Rank10", Int64.Type}, {"Rank11", Int64.Type}, {"Rank12", Int64.Type}, {"Rank13", Int64.Type}, {"Rank14", Int64.Type}, {"Rank15", Int64.Type}, {"Rank16", Int64.Type}, {"Rank17", Int64.Type}, {"Rank18", Int64.Type}, {"Rank19", Int64.Type}, {"Rank20", Int64.Type}}),#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Item", "Overall Rank", "Average"}),#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Overall Rank", Order.Ascending}})in#"Sorted Rows"`