Automating The Selection of High Values in Excel

Question

I have Excel Data that looks like this:LastSiteTestScoreAmayaOneOps Skills100BrownTwoOps Skills100GarciaOneOps Skills100GarciaOneOps Skills67JonesThreeOps Skills71LiuFourOps Skills90NguyenOneOps Skills85NovakTwoFinance81NovakTwoFinance58PatelFourOps Skills100SmithTwoOps Skills100XiThreeFinance90&nbsp;I would like to select the high value for an individual user and test, automatically, with the query feature in Excel so that I am left with just the high values for each user and test.&nbsp; I'd like the data to look like this:LastSiteTestScoreAmayaOneOps Skills100BrownTwoOps Skills100GarciaOneOps Skills100JonesThreeOps Skills71LiuFourOps Skills90NguyenOneOps Skills85NovakTwoFinance81PatelFourOps Skills100SmithTwoOps Skills100XiThreeFinance90&nbsp;How might I do this in Excel 2016?

sergeibaklan · Answer

inmansteven&nbsp;
If with Power Query you may query the source, sort ascending names and descending scores, fix in memory by adding index, remove duplicates by all columns but score, remove index, return result into Excel sheet.

inmansteven · Answer

SergeiBaklan&nbsp;Thank you.&nbsp; This was very helpful.&nbsp;&nbsp;

sergeibaklan · Answer

inmansteven&nbsp;, you are welcome

Forum Discussion

Automating The Selection of High Values in Excel

3 Replies

Resources

Last	Site	Test	Score
Amaya	One	Ops Skills	100
Brown	Two	Ops Skills	100
Garcia	One	Ops Skills	100
Garcia	One	Ops Skills	67
Jones	Three	Ops Skills	71
Liu	Four	Ops Skills	90
Nguyen	One	Ops Skills	85
Novak	Two	Finance	81
Novak	Two	Finance	58
Patel	Four	Ops Skills	100
Smith	Two	Ops Skills	100
Xi	Three	Finance	90