Squashed List

Remove blank entries from a list. Useful for mail-merge.

File download: squashed_list.zip

This example is all formula driven - no VBA.

Select cell F2

Select a name ...

Column F shows the results picked from the address table (a range called AddressTable - not shown here, but is in the example file and ready for you to customise)

Column I shows the same information but has excluded the blank lines.

Here's the formula in F4.
In the table AddressTable, it looks up the name selected in F2.
C4 is a cell reference showing the column in which holds the data we want to see.

=VLOOKUP($F$2,AddressTable,C4,FALSE)

Here's the formula in E4. It adds 1 to the value in the cell above, but only if there is some text in cell F4.

=E3+IF(ISTEXT(F4),1,0)

Here's the formula in cell I4.
The range SquashList is range E4:F12.

=IF(ISNA(VLOOKUP(H4,SquashList,2,FALSE)),"",VLOOKUP(H4,SquashList,2,FALSE))

See also:

Published: 12-Jun-2005
Last edited: 01-Mar-2011 20:50