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

Try this formula from Chip Pearson's website.

http://www.cpearson.com/excel/noblanks.htm

I don't think you need VBA.

Cheers,

Gromit



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

Oops - I must have confused two different problems (one I'm working
on privately), since you didn't specify unique values and you said
the formula returns nothing, which I assume means a null string.

In the manual solution below, change the formula to

=AND(ISTEXT(A2), LEN(A2)0)

and don't check the unique checkbox.

In the macro, use:

Public Sub CopyText()
Application.ScreenUpdating = False
With Range("D2")
.FormulaR1C1 = "=AND(ISTEXT(rc1),LEN(rc1)0)"
Range("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Offset(-1, 0).Resize(2, 1), _
CopyToRange:=Range("B1"), _
Unique:=False
.ClearContents
End With
End Sub


In article
,
"J.E. McGimpsey" wrote:

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

Thanks, I have to copy the data in Columns A, B, C etc to a single column to
make the array work and then use If statements to put in "" instead of 0 to
make the noblanks work, but once I've done that it works great. It's a
little messy but I'm a lot further ahead than I was!

I'm still looking for an elegant VB solution though that allows me to do the
calculations in VB and requires no cells at all except those for the final
data. I know it's hiding out there somewhere...

Cheers,

Andy





"Gromit" wrote in message
...
Try this formula from Chip Pearson's website.

http://www.cpearson.com/excel/noblanks.htm

I don't think you need VBA.

Cheers,

Gromit



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/