excel - Create matrix from multiple values in a single cell -
i have data in excel: that want convert (5*7) matrix this: sara jone shery jack joe peter beti book1 1 0 0 0 0 0 0 book2 1 0 0 1 0 0 1 book3 0 0 0 0 0 1 0 book4 0 1 1 1 0 0 0 book5 0 0 1 0 1 0 0 what can do? my choice rearrange data suit pivottable. copy columnc , paste special values columnd. replace blanks in columnd nothing (to remove space book1, book2 ). select columnd, data > data tools - text columns, delimited, comma filter columne select non blank rows , copy visible part of columnb (excluding header) b9 (or suit) , visible part of columne (excluding header) d9 (or suit). create pivottable b1:d11 (or suit) shown (using columnd belong - ie belong2 ):