A text based pivot table
May 17, 2010 2:25 PM   Subscribe

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 (9 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 = ""
        currentVal = Cells(i, 1).Value
    End If
    Next i

End Sub

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

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

Not sure if this is exactly what you need, but if I had your starting data, and wanted to get to your ending data, here's what I'd do:

1. Add a column to the right of the date column and add a function like '=IF(A2=A1,"",A2)'. This brings over the date from the right when it's different from the one above it, but otherwise leaves the cell blank. Autofill this function down through the end of your data.

2. Add a column at the far right that's something like '=AND(C2="",D2="",E2="")'. This gives you a TRUE if all three cells are blank and a FALSE otherwise. Autofill through the end of your data.

3. Turn on Autofilter and filter based on the value in that last row (hiding the TRUEs).

That leaves you with a view that's pretty much what you're looking for. (Hide the original date column and the far right column if you want a prettier view.)

Will this approach work for your actual live dataset? Hard to say. But it's a quick way to go from what you have to what you want (in your example anway). and you could write a macro or VB function to apply these changes quickly and automatically.
posted by Two unicycles and some duct tape at 3:29 PM on May 17, 2010

Here is the link to the google spreadsheet. Great suggestion, dfriedman

posted by yoyoceramic at 3:32 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

Ah, if I'm reading this right you seem to be rolling the 'food' data upward, filling in blanks, as well as hiding rows that are completely blank and hiding duplicate dates. My solution won't help you consolidate the 'food' data under each date, and I can't really think of a good way to do that kind of rollup that wouldn't be pretty ugly. I'll defer to bigger brains than mine, then. Good luck!
posted by Two unicycles and some duct tape at 4:00 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

« Older Windows 2008 Tape Backup?   |   local boston moving help! Newer »
This thread is closed to new comments.