One way:

Manually:

In an empty column (say D), leave D1 blank and enter

D2: =ISTEXT(A2)

Choose Data/Filter/Advanced Filter. Select the Copy to new location
radio button. Check the Unique checkbox. Enter $A:$A in the source
range, $D$1:$D$2 in the criteria range, and $B$1 in the destination
range. Click OK.

To do it in a macro:

Public Sub CopyText()
With Range("D2")
.FormulaR1C1 = "=ISTEXT(rc1)"
Range("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.offset(-1,0).resize(2,1), _
CopyToRange:=Range("B1"), _
Unique:=True
.ClearContents
End With
End Sub

Change D2 to suit.


In article ,
"Andy" wrote:

In many ways an alternative to my prior post about ranges with spaces, I'm
hoping this is easier.

I want to take a number of If statements, all of which return a text string,
"Sample" for example, if true or nothing if not and have them generate a
list.

I then want to put that list in order into cells on the spreadsheet. So in
layman's language (I know it's not VBA...)

If A1 = 1 Then
Add "Sample" to list

If A2 = 5 Then
Add "Example" to list

Place the list in Cells B1:B2 in the order they appear above.

It's so simple when written like that but the adding to list and placing in
spreadsheet components defeat me.

Advice welcome.

Andy