Forum Discussion

mmrothsc's avatar
mmrothsc
Copper Contributor
Jul 29, 2021

How to return next largest observation of text column

Hi, 

 

Attached is an example file of the column of data I have (Column B) and hardcoded version of data I want (column C), which is also shown below.  If possible, I'd like to do this without an array formula, but SUMPRODUCT with multiple conditions would be fine.  If helpful, my actual spreadsheet also has each of the items separated by " - " under the "Example Data" below in separate columns.

 

Thanks!

 

Example DataIntended Output
AW139 Long Nose - AW139 - 2007 - 2020AW139 Long Nose - AW139 - 2007 - 2019
AW139 Long Nose - AW139 - 2007 - 2019AW139 Long Nose - AW139 - 2007 - 2017
AW139 Long Nose - AW139 - 2007 - 2019AW139 Long Nose - AW139 - 2007 - 2017
AW139 Long Nose - AW139 - 2007 - 2017AW139 Long Nose - AW139 - 2007 - 2016
AW139 Long Nose - AW139 - 2007 - 2016AW139 Long Nose - AW139 - 2007 - 2015
AW139 Long Nose - AW139 - 2007 - 2015AW139 Long Nose - AW139 - 2007 - 2013
AW139 Long Nose - AW139 - 2007 - 2015AW139 Long Nose - AW139 - 2007 - 2013
AW139 Long Nose - AW139 - 2007 - 2013AW139 Long Nose - AW139 - 2007 - 2012
AW139 Long Nose - AW139 - 2007 - 2012AW139 Long Nose - AW139 - 2007 - 2011
AW139 Long Nose - AW139 - 2007 - 2011AW139 Long Nose - AW139 - 2007 - 2010
AW139 Long Nose - AW139 - 2007 - 2010AW139 Long Nose - AW139 - 2007 - 2009
AW139 Long Nose - AW139 - 2007 - 2009 
AW139 Long Nose - AW139 - 2007 - 2009 
AW139 Short Nose - AW139 - 2007 - 2020AW139 Short Nose - AW139 - 2007 - 2019
AW139 Short Nose - AW139 - 2007 - 2019AW139 Short Nose - AW139 - 2007 - 2017
AW139 Short Nose - AW139 - 2007 - 2017AW139 Short Nose - AW139 - 2007 - 2016
AW139 Short Nose - AW139 - 2007 - 2017AW139 Short Nose - AW139 - 2007 - 2016
AW139 Short Nose - AW139 - 2007 - 2016AW139 Short Nose - AW139 - 2007 - 2015
AW139 Short Nose - AW139 - 2007 - 2015AW139 Short Nose - AW139 - 2007 - 2013
AW139 Short Nose - AW139 - 2007 - 2015AW139 Short Nose - AW139 - 2007 - 2013
AW139 Short Nose - AW139 - 2007 - 2013AW139 Short Nose - AW139 - 2007 - 2012
AW139 Short Nose - AW139 - 2007 - 2012AW139 Short Nose - AW139 - 2007 - 2011
AW139 Short Nose - AW139 - 2007 - 2011AW139 Short Nose - AW139 - 2007 - 2010
AW139 Short Nose - AW139 - 2007 - 2010AW139 Short Nose - AW139 - 2007 - 2009
AW139 Short Nose - AW139 - 2007 - 2009 
AW139 Short Nose - AW139 - 2007 - 2009 
excel
Formulas and Functions

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 30, 2021

    mmrothsc Transformed your example data to an Excel table and connected to it with Power Query (=Get & Transform Data, on the Data ribbon). Select "From Sheet" (or From Table/Range on older Excel versions). PQ is built-in in Excel 2016 and later and available as an add-in for older versions.

     

    With a few clicks on some buttons you can create the table like in Column E. See attached. Something worth exploring/learning if you have not used PQ before. No need for complicated formulae. If you have used PQ before, just follow the Applied Steps that I have used. 

Resources