SOLVED
Home

Sorted Ranked Data on separate sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-612639%22%20slang%3D%22en-US%22%3ESorted%20Ranked%20Data%20on%20separate%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612639%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20get%20the%20ranked%20data%20on%20a%20separate%20sheet%20that%20is%20sorted%20with%20formula%3F%3C%2FP%3E%3CP%3ETwo%20sheets%20I%20have%2C%20data%20and%20summary.%20The%20data%20sheet%20has%20items%20ranked.%20There%20are%203%20columns%20that%20I%20want%20to%20show%20in%20the%20next%20summary%20sheet%20that%20is%20in%20ascending%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJay.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-612639%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612695%22%20slang%3D%22en-US%22%3ERe%3A%20Sorted%20Ranked%20Data%20on%20separate%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612695%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330883%22%20target%3D%22_blank%22%3E%40AVP68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20probably%20would%20do%20it%20without%20formulas.%3C%2FP%3E%3CPRE%3E%3DINDEX(Data!%24A%242%3A%24A%246%2CMATCH(B2%2CData!%24B%242%3A%24B%246%2C0))%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSMALL(Data!%24B%242%3A%24B%246%2CROWS(B%242%3AB2))%3CBR%20%2F%3E%3CBR%20%2F%3E%3DINDEX(Data!%24C%242%3A%24C%246%2CMATCH(B2%2CData!%24B%242%3A%24B%246%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612698%22%20slang%3D%22en-US%22%3ERe%3A%20Sorted%20Ranked%20Data%20on%20separate%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20Thanks%20that%20works.%20I%20would%20agree%20that%20VBA%20code%20may%20have%20been%20a%20better%20approach%20but%20that%20is%20beyond%20my%20scope%20and%20I%20am%20happy%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612785%22%20slang%3D%22en-US%22%3ERe%3A%20Sorted%20Ranked%20Data%20on%20separate%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330883%22%20target%3D%22_blank%22%3E%40AVP68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wasn't%20thinking%20of%20VBA%20but%20of%20PQ.%3C%2FP%3E%3CPRE%3Elet%3CBR%20%2F%3ESource%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22tbl_Data%22%5D%7D%5BContent%5D%2C%3CBR%20%2F%3E%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(Source%2C%7B%7B%22Item%22%2C%20type%20text%7D%2C%20%7B%22Overall%20Rank%22%2C%20Int64.Type%7D%2C%20%7B%22Average%22%2C%20type%20number%7D%2C%20%7B%22Rank1%22%2C%20Int64.Type%7D%2C%20%7B%22Rank2%22%2C%20Int64.Type%7D%2C%20%7B%22Rank3%22%2C%20Int64.Type%7D%2C%20%7B%22Rank4%22%2C%20Int64.Type%7D%2C%20%7B%22Rank5%22%2C%20Int64.Type%7D%2C%20%7B%22Rank6%22%2C%20Int64.Type%7D%2C%20%7B%22Rank7%22%2C%20Int64.Type%7D%2C%20%7B%22Rank8%22%2C%20Int64.Type%7D%2C%20%7B%22Rank9%22%2C%20Int64.Type%7D%2C%20%7B%22Rank10%22%2C%20Int64.Type%7D%2C%20%7B%22Rank11%22%2C%20Int64.Type%7D%2C%20%7B%22Rank12%22%2C%20Int64.Type%7D%2C%20%7B%22Rank13%22%2C%20Int64.Type%7D%2C%20%7B%22Rank14%22%2C%20Int64.Type%7D%2C%20%7B%22Rank15%22%2C%20Int64.Type%7D%2C%20%7B%22Rank16%22%2C%20Int64.Type%7D%2C%20%7B%22Rank17%22%2C%20Int64.Type%7D%2C%20%7B%22Rank18%22%2C%20Int64.Type%7D%2C%20%7B%22Rank19%22%2C%20Int64.Type%7D%2C%20%7B%22Rank20%22%2C%20Int64.Type%7D%7D)%2C%3CBR%20%2F%3E%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%23%22Changed%20Type%22%2C%7B%22Item%22%2C%20%22Overall%20Rank%22%2C%20%22Average%22%7D)%2C%3CBR%20%2F%3E%23%22Sorted%20Rows%22%20%3D%20Table.Sort(%23%22Removed%20Other%20Columns%22%2C%7B%7B%22Overall%20Rank%22%2C%20Order.Ascending%7D%7D)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Sorted%20Rows%22%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
AVP68
Occasional Contributor

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
Solution

@AVP68 

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))

 

@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.

@AVP68 

I wasn't thinking of VBA but of PQ.

let
Source = 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"