No more gaps – a formula to remove spaces in lists or empty rows in ranges
Sometimes you just need to get rid of the gaps in lists! Perhaps you want to use the list for a validation dropdown and you want no gaps in the list so it’s easier to use. Perhaps you just want to create a better looking table from a long list of values.
Note carefully where the anchoring is row being the start of the list in column B, row 30 being the end including some spaces. The formula is copied down until all the items in B appear in the new list. in column E.
But there are situations where you have many rows of data and formulas on each row highlighting or extracting something but you need to neatly summarise. Well the above formula is ready to be dragged across to pick up the next columns data and so on.