In a spreadsheet (Excel, Google Docs, OpenOffice), how do I transpose data from rows to columns, but only non-null cells?

"NoCatharsis,

The simple answer I have for you is Excel specific (Google Docs didn't have similar filters and Open Office's Calc copies filtered cells):

1) Use ""AutoFilter"" (Data>Filter>AutoFilter) and filter for non-blanks.

2) Copy remaining cells

3) Paste Special (Edit>Paste Special) and select ""Transpose"" from the options

JDB"

"I can't think of a way to do this without macro programming, so I'm not sure about Google Docs or OpenOffice, but here is one way to do it in Excel.

This macro assumes:

Your data is on a separate sheet named ""Sheet1""
Your destination is a blank sheet named ""Sheet2""

To use the macro, select the rows you want to transpose on Sheet1, then execute the macro with the rows higlighted. The results should be copied to Sheet2.

Sub CopyNonNulls()

Dim DestinationCellAddress As String
DestinationCellAddress = ""A1""

Dim CurrentCellAddress As String
CurrentCellAddress = DestinationCellAddress

For Each rw In Selection.Rows

For Each c In rw.Cells

If c.Value <> """" Then
Worksheets(""Sheet2"").Range(CurrentCellAddress).Value = c.Value
CurrentCellAddress = Worksheets(""Sheet2"").Range(CurrentCellAddress).Offset(1, 0).Address
End If

Next

CurrentCellAddress = Worksheets(""Sheet2"").Range(DestinationCellAddress).Offset(0, 1).Address

Next

Worksheets(""Sheet2"").Select

End Sub"