Transforming table's content/display

  • Thread starter Thread starter noa
  • Start date Start date
N

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
 
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.
 
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.
 
Rightclick on the pivottable
Choose table options
Check "for empty cells, show:"
and type in 0 in that box.
 
Back
Top