Search code examples
excelexcel-formulaexcel-2007pivot-tablevlookup

Create matrix from multiple values in a single cell

I have data like this in Excel:

SO18318488 question example

that I want to convert to a (5*7) matrix like 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 I do?

Solution

  • My choice would be to rearrange the data to suit a PivotTable. So copy ColumnC and Paste Special Values into ColumnD. Then replace blanks in ColumnD with nothing (to remove the space from book1, book2). Select ColumnD, Data > Data Tools - Text to Columns, Delimited, Comma then filter ColumnE to select non blank rows and copy the visible part of ColumnB (excluding header) to B9 (or to suit) and the visible part of ColumnE (excluding header) to D9 (or to suit). Then create your PivotTable from B1:D11 (or to suit) as shown (using ColumnD for belong - ie belong2):

    SO18318488 example