removing blanks from list by formula

**reggie1000** · 12-27-2007, 11:06 AM

Good Afternoon,

I was having a problem with regards to sorting a list by formula by removing the blanks without having to sort the column, i.e in a list of number in column A 1 - 25 but numbers 9,13,14,21,22 are missing, in column B the numbers should be in sequence with no blank cells in the list, i can't seem to do it without "sorting" which rearranges the cell references.

hopefully someone can figure it out?

cheers reg

**Ron Coderre** · 12-27-2007, 12:06 PM

Using your posted file...
Since it appears that there are no duplicate values in your source list...

Try this:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Does that help?

**reggie1000** · 12-28-2007, 04:52 AM

Thank you so much Ron,

thats brilliant

cheers reg

**reggie1000** · 12-28-2007, 05:21 AM

I have another question Ron,

i have added some text to the list but the formula only calculates numbers, is ther a way of including text as well, i have re attached a sample,

thanks reg

**oldchippy** · 12-28-2007, 05:58 AM

Try this array formula in H1, but use Ctrl+Shift+Enter to place { } around the formula

=IF(ISNUMBER(SMALL(IF(F$1:F$30<>"",ROW(F$1:F$30)),ROW())),INDEX(F$1:F$30,SMALL(IF(F$1:F$30<>"",ROW(F$1:F$30)),ROW()),1),"")

**reggie1000** · 12-28-2007, 06:50 AM

Thanks once again Old Chippy,

cheers reg

**oldchippy** · 12-29-2007, 01:01 PM

Glad to hear it helped - thanks for the feedback

removing blanks from list by formula

LinkBack

Thread Tools

Rate This Thread

Display

removing blanks from list by formula

removing blanks from list by formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions