Create a list of uppercase data from a range excluding blanks and errors

I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.

Example:

AA DD
#N/A
BB 23 EE FF
#N/A
#N/A 12 GG
CC


Results:

AA
BB
CC
DD
EE
FF
GG


Thanks,

PAexcel

Is your data means Alpha values are always two character and Are they arranged with same character like AA,BB,CC or like AB, AC, AD, AF and so on.

Hi,

Why do you specify "upper case" in your title? Are there also some lower case entries which you haven't given examples of and which are not to be returned?

Regards

Hi,

Assuming the data you give it is in A1:D6, go to Name Manager and create the following new names:

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&SUM(IF(1-ISNA($A$1:$D$6),IF(1-ISNUMBER($A$1:$D$6),IF($A$1:$D$6<>"",IF(EXACT(UPPER($A$1:$D$6),$A$1:$D$6),1)))))))

Name: Arry2
Refers to: =T(INDIRECT(IF(1,TEXT(SMALL(IF(1-ISNA($A$1:$D$6),IF(1-ISNUMBER($A$1:$D$6),IF($A$1:$D$6<>"",IF(EXACT(UPPER($A$1:$D$6),$A$1:$D$6),10^3*ROW($A$1:$D$6)+COLUMN($A$1:$D$6))))),Arry1),"R000C000")),0))

(When you paste these names in, double-check that they are exactly as they appear here.)

Exit Name Manager.

Enter this array formula** in your first cell of choice:

=IFERROR(INDEX(Arry2,MATCH(SMALL(MMULT(0+(Arry2>TRANSPOSE(Arry2)),Arry1^0),ROWS($1:1)),MMULT(0+(Arry2>TRANSPOSE(Arry2)),Arry1^0),0)),"")

Copy down until you start to get blanks.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome.