I have a spreadsheet where users enter various year-end data. Each year may have multiple records. Let's say column A contains the year. Sometimes the users don't enter the years in order (e.g. 2015, 2014, 2013, etc.) and sometimes there are blank spaces between years. I am trying to use formulas to create a summary table of the data by year.
Some example input is (say this is column A):
+---+------+
| | A |
+---+------+
| 1 | |
+---+------+
| 2 | 2013 |
+---+------+
| 3 | 2015 |
+---+------+
| 4 | |
+---+------+
| 5 | 2014 |
+---+------+
| 6 | 2015 |
+---+------+
| 7 | 2015 |
+---+------+
| 8 | |
+---+------+
And my desired output would be (say in column C):
+---+------+
| | C |
+---+------+
| 1 | 2015 |
+---+------+
| 2 | 2014 |
+---+------+
| 3 | 2013 |
+---+------+
Notice how there are no duplicates, blanks are removed, and the order is sorted by year descending.
I know I will need to use an array formula, and in fact I have one that mostly works (an array formula with a mix of index, match, sum, and countif). The issue arises when the first cell (A1 in my example input above) is empty, the formula fails, but otherwise it works. Since I cannot reliably say people will always start their data in cell A1 I need to correct this.
I cannot use a macro (which is more my style in Excel, advanced formulas definitely are not) because the requirement is that it is saved as a .xlsx file.
Thank you for your time.
