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
):
Comments
Post a Comment