A text based pivot table

May 17, 2010 2:25 PM

Excel Assist/I don't believe I couldn't figure this one out: I have a table with unique text values. How can I have it act the same way as a pivot table? Sample inside.

Here is a simplified version of my excel table. Columns are names of individuals, rows are dates. The data in the table consists of a food item (for example's sake). Not all cells in the table contain a value.

Here is an example of what I have vs an Example of what I would like to see:

Go from this:


_________| Bob | Jill | Rick
1/13/2009 Pear Fish Beets
1/13/2009 Apples [blank] grapes
1/13/2009 [blank] [blank] [blank]
1/20/2009 Broccoli tomato potato
1/20/2009 pepper salt [blank]
1/20/2009 [blank] [blank] [blank]
1/20/2009 [blank] [blank] [blank]
1/26/2009 banana peach mushroom
1/26/2009 eggplant [blank] [blank]
1/26/2009 milk cheese spinach


To this:

_________| Bob | Jill | Rick
1/13/2009 Pear Fish Beets
.............Apples [blank] grapes
1/20/2009 Broccoli tomato potato
.............pepper salt [blank]
1/26/2009 banana peach mushroom
.............eggplant cheese spinach
.............milk [blank] [blank]


As you can see, basically what I would like to happen is a condensation of all of the unnecessary [blank] (empty) cells without affecting the meaning of the data. Any thoughts here?

posted by yoyoceramic to Computers & Internet (7 answers total)

It's really hard to read the sample data you've provided and infer from that how it looks in an Excel file.

But, if I understand you correctly, you have a table, for which some of the cells contain no values, and you want this table to operate as a pivot table?

Why can't you just create a pivot table from the data table you have and go from there? Or am I missing something? It may help, as well, to paste a sample of your data into a google docs spreadsheet and provide a link (assuming there's no confidential information) so that people can see what you're talking about in a better format.
posted by dfriedman at 2:43 PM on May 17, 2010

If I understand what you want, it's just if the date in row X = the date in row X+1, then you want the cell with the date in X+1 to be blank. Right? If so, this brute force macro should work:

Sub RemoveRepeats()
'
' Assumes the data is in Column A, and in rows from 1 to the last row used.
'

Dim currentVal As String
Dim i As Integer
Dim lastRow As Integer

i = 1 ' set this to the first row with data
currentVal = Cells(i, 1).Value
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

i = i + 1

For i = 2 To lastRow
    
    If Cells(i, 1).Value = currentVal Then
        Cells(i, 1).Value = ""
        Else
        currentVal = Cells(i, 1).Value
    End If
    Next i

End Sub

posted by inigo2 at 2:49 PM on May 17, 2010

Response by poster: Why can't you just create a pivot table from the data table you have and go from there?

Pivot tables cannot organize data that is not numerical.
posted by yoyoceramic at 3:08 PM on May 17, 2010

Response by poster: Here is the link to the google spreadsheet. Great suggestion, dfriedman

http://spreadsheets.google.com/ccc?key=0Ai41XkWa4cLYdFRoSXhXWDdtcGZsWlBqOFpvNUJqaUE&hl=en
posted by yoyoceramic at 3:32 PM on May 17, 2010

Response by poster: http://spreadsheets.google.com/ccc?key=0Ai41XkWa4cLYdFRoSXhXWDdtcGZsWlBqOFpvNUJqaUE&hl=en
posted by yoyoceramic at 3:33 PM on May 17, 2010

To add to Two unicycles suggestion above, you could custom sort the data "before" doing those steps, so that all the "blanks" are grouped together at the bottom/top, then delete them. Then sort back by date. You don't need to do steps 2 and 3 if you do this, just step 1 should work.
posted by prenominal at 3:57 PM on May 17, 2010

Wait, you're doing two different things depending on the row.
In 1/26, you move everything up to the top 1/26, so all 1/26 blanks are below the 1/26 data.
In 1/20, you don't (you leave a blank 1/20 above "mushroom" in the last column).

So....hopefully this made sense, but not sure quite what you want still.
posted by inigo2 at 5:11 PM on May 17, 2010