help with crosstab

  • Thread starter Thread starter ngohwoonhiong
  • Start date Start date
N

ngohwoonhiong

let's say i have the following data in a list
Date Time Change
1/1/2000 0900 +
1/1/2000 0915 +
1/1/2000 0930 -
1/1/2001 0900 -
1/1/2001 0915 +
1/1/2001 0930 +

i would like to create a cross tab that looks like this

Date 0900 0915 0930
1/1/2000 + + -
1/1/2001 - + +

i don't want to display any sum or count with the data, i just want t
display + and - as it is.

thanks for any comments
 
I think I'd do this--if there were no duplicates in the date/times.

(You may want to save your work before--just in case it doesn't work for you.)

Select column C (Change) and
Edit|replace (twice)
change + to 1 (number 1)
change - to -1 (negative 1)

Now select your range (a1:c9999) and
data|Pivottable

since your range is selected, you can just advance through the wizard until you
get to the step that has a "layout" button on it.

Click on Layout
drag Date to the Row field
drag Time to the Column field
drag change to the data field
(if excel shows "Count of Change", then double click on it and choose Sum.)

Click finish/ok

Now you can can convert this pivottable to data.
Ctrl-A to select the all the cells
Edit|copy
edit|paste special|Values

Select that smaller range of -1 and 1's
Now change -1 back to -
change 1 to +

Get rid of the grand total column and the grand total row.

and remember to change the 1/-1 back to +/- on the original sheet.
 
Or, after you create the pivotTable, format the data to display as plus
and minus signs:

In the pivotTable, select the data.
Choose Format>Cells, and select the Number tab
Select the Custom category
In the text box, type: +;-
Click OK
 
Back
Top