Posts Tagged ‘function INDIRECT()’

Excel and palindromes (continuation)

January 10th, 2010

Palindrome list

In my last post about Excel and palindromes I showed you how to check if a sentence is a palindrome. Today, based on the list of different sentences (including palindromes) we will prepare an automatically generated list containing only palindromes.

Excel and list of palindromes

We will use a helper column (column F in my case). Let’s select the amount of cells in our helper column to equal the number of sentences in our source list. In my case it’s 7 cells.

Excel and palindromes
Next, let’s type in the following array formula:

{=SMALL(IF($D$8:$D$14,ROW($D$8:$D$14),""),ROW(INDIRECT("1:"&COUNT(IF($D$8:$D$14,ROW($D$8:$D$14),"")))))}

The formula uses the “is palindrome?” column ($D$8:$D$14) we talked about last time. For each found palindrome it returns the palindrome’s row number, and the error value #N/A otherwise.

What’s left now is to type the following formula into the first cell of my palindrome’s list:

=IF(ISERROR(INDIRECT(ADDRESS(F8,2))),"",INDIRECT(ADDRESS(F8,2)))

We could alternatively use a simpler formula:

=IF(ISERROR(F8),"",INDEX($B:$B,F8))

and copy it down to the cells below. Should work.

Marcin