Transforming table's content/display

[noa]

I don’t know if this is possible.
I would like to change a tables’s data from the following presentation:

id selection
1 50
1 60
1 70
2 50
2 80
2 90

to this one:

id 50 60 70 80 90
1 1 1 1 0 0
2 1 0 0 1 1


Can I do that?
Thanks,
Noa

 

[akphidelt]

This is kind of a work around, maybe not the best suggestion but it works.

Highlight the data, go to Data--->Pivot Table And PivotChart Report

Click on Finish

Draft the Selection field in to the column heading field of the pivot table

Then drag the ID field in to the row heading field

Then drag the ID field again in to the detail section of the pivot table

Right click on the detail section, click on Field Settings and change that
from Sum to Count.

haha, worked for me, but might not work if you have a massive amount of data.

 

[Dave Peterson]

It looks like the perfect layout to use a pivottable.

In xl2003:

Select A1:Bxxx
data|pivottable
Next
Next
Layout

Drag the Id header to the Row field
drag the selection header to the column field
drag the selection header (again) to the data field
But rightclick on that one and choose Count of (not sum)

And finish up the wizard.

 

[noa]

Thanks,
It works. I just have to add 'zeros' to the empty cells.
Noa

 

[Dave Peterson]

Rightclick on the pivottable
Choose table options
Check "for empty cells, show:"
and type in 0 in that box.