Jul 29 2021 09:10 AM
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 Data | Intended Output |
AW139 Long Nose - AW139 - 2007 - 2020 | AW139 Long Nose - AW139 - 2007 - 2019 |
AW139 Long Nose - AW139 - 2007 - 2019 | AW139 Long Nose - AW139 - 2007 - 2017 |
AW139 Long Nose - AW139 - 2007 - 2019 | AW139 Long Nose - AW139 - 2007 - 2017 |
AW139 Long Nose - AW139 - 2007 - 2017 | AW139 Long Nose - AW139 - 2007 - 2016 |
AW139 Long Nose - AW139 - 2007 - 2016 | AW139 Long Nose - AW139 - 2007 - 2015 |
AW139 Long Nose - AW139 - 2007 - 2015 | AW139 Long Nose - AW139 - 2007 - 2013 |
AW139 Long Nose - AW139 - 2007 - 2015 | AW139 Long Nose - AW139 - 2007 - 2013 |
AW139 Long Nose - AW139 - 2007 - 2013 | AW139 Long Nose - AW139 - 2007 - 2012 |
AW139 Long Nose - AW139 - 2007 - 2012 | AW139 Long Nose - AW139 - 2007 - 2011 |
AW139 Long Nose - AW139 - 2007 - 2011 | AW139 Long Nose - AW139 - 2007 - 2010 |
AW139 Long Nose - AW139 - 2007 - 2010 | AW139 Long Nose - AW139 - 2007 - 2009 |
AW139 Long Nose - AW139 - 2007 - 2009 | |
AW139 Long Nose - AW139 - 2007 - 2009 | |
AW139 Short Nose - AW139 - 2007 - 2020 | AW139 Short Nose - AW139 - 2007 - 2019 |
AW139 Short Nose - AW139 - 2007 - 2019 | AW139 Short Nose - AW139 - 2007 - 2017 |
AW139 Short Nose - AW139 - 2007 - 2017 | AW139 Short Nose - AW139 - 2007 - 2016 |
AW139 Short Nose - AW139 - 2007 - 2017 | AW139 Short Nose - AW139 - 2007 - 2016 |
AW139 Short Nose - AW139 - 2007 - 2016 | AW139 Short Nose - AW139 - 2007 - 2015 |
AW139 Short Nose - AW139 - 2007 - 2015 | AW139 Short Nose - AW139 - 2007 - 2013 |
AW139 Short Nose - AW139 - 2007 - 2015 | AW139 Short Nose - AW139 - 2007 - 2013 |
AW139 Short Nose - AW139 - 2007 - 2013 | AW139 Short Nose - AW139 - 2007 - 2012 |
AW139 Short Nose - AW139 - 2007 - 2012 | AW139 Short Nose - AW139 - 2007 - 2011 |
AW139 Short Nose - AW139 - 2007 - 2011 | AW139 Short Nose - AW139 - 2007 - 2010 |
AW139 Short Nose - AW139 - 2007 - 2010 | AW139 Short Nose - AW139 - 2007 - 2009 |
AW139 Short Nose - AW139 - 2007 - 2009 | |
AW139 Short Nose - AW139 - 2007 - 2009 |
Jul 29 2021 11:41 PM
@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.