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

Question

I work with all three spreadsheet programs listed above, and I believe my question can be answered the same or similarly for all three.

On my sheet, I have a top row full of column headers. The second row is data corresponding to the column header, but some of the cells may or may not be empty. I would like to transpose the data in this second row so that the new column ignores null cells altogether.

For example, if I have a row with the values "A", NULL, "C", "D" in sequential cells, I want to transpose such that the new column appears "A", "C", "D" with no empty cell for the NULL.

Hope my description is, well, descriptive. Thanks.

Solution 1

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

Solution 2

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:

1. Your data is on a separate sheet named "Sheet1"
2. 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

Solution 3

Given the specific example starting with a row and ending up with a column, I would do the same steps the other way round, although I don't like the idea of ending up with a filtered sheet. If you can sort the data (ie the order does not matter) that would get all the blanks to one place where you can delete out the rows.

Anyway, I propose a better all round solution using advanced filter: select your row of original data, copy, select a cell somewhere on a new sheet with at least one empty cell above it, paste > paste special, tick 'transpose', OK

Now you have a column with gaps.

In the cell above it, give the column a label, say "data". Make it bold (Ctrl-B) so Excel will spot this is a title even though it is text just like the rest of the column.

In another cell somewhere (eg two cells to the right), type the same label, and in the cell beneath an asterisk (* - a wildcard for later). Select your column with all its gaps and header

Go to Data ribbon > sort and filter group and click on Advanced Filter.

Tick "Copy to another location"

Check the "list range" has your selected column of data.

Click the refedit button next to "Criteria range" and select the two cells you created before with the header and wildcard

For the "copy to" range select any cell on this sheet, eg a couple more columns over to the right.

Click OK.

Job done. You now have a new set of contiguous data and you can delete the other columns.

Note that you can't use advanced filter to copy the new list to another sheet except if you start from that sheet in the first place, then come back to select the data and criteria. For a quick and dirty approach, this is a lot simpler.

Solution 4

You can do this through the TRANSPOSE function in Google Spreadsheets. Find their explanation on how to do it here: http://docs.google.com/support/bin/answer.py?hl=en&answer=71291

View more solutions