Forum Discussion
Formulas Challenge for 365
Thank you for reading this post! I propose a formulas challenge (The shorter the better). Attached is a workbook in need of two formulas. I have solutions for each (Formula 1 is 66 characters, ...
Patrick2788 A short formula isn't always best :))
For number 1 I could come up with this (at 72 char):
=VSTACK('1'!A1:B1,TAKE(SORT(VSTACK('1:5'!A2:B30)),COUNTA('1:5'!A2:A30)))I feel that there must be a better way to exclude the blanks asThe COUNTA at the end looks a bit clumsy. And if you hadn't insisted on a formula generated header, the count would have come down to 54 chars.
I used DROP and UNIQUE to take care of the blank.
=VSTACK('1'!A1:B1,DROP(SORT(UNIQUE(VSTACK('1:5'!A1:B30)),1,1),-1))
=VSTACK('1'!A1:B1,DROP(SORT(UNIQUE(VSTACK('1:5'!A1:B30)),1,1),-1))