Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Complex cross tabulation problem - Kindly help please

Latest post Wed, Jul 2 2014 4:48 PM by Emess. 2 replies.
  • Tue, Jul 1 2014 7:08 PM

    • Emess
    • Not Ranked
    • Joined on Tue, Jul 1 2014
    • Posts 2
    • Points 26

    Complex cross tabulation problem - Kindly help please

    I have below a small sample output of data that looks as follows, referring to vaccines received by patients on various dates:

    PtID VaccName VaccDate
    pt001 Vacc1 DateVacc1
    pt001 Vacc2 DateVacc2
    pt001 Vacc3 DateVacc3
    pt002 Vacc2 DateVacc2
    pt003 Vacc1 DateVacc2
    pt003 Vacc3 DateVacc3

     Here is how I want to re-arrange the data. I am using Excel 2007. Pivot Tables do not do the job; I have been unsuccessful with various LOOKUP functions. Any ideas please? PS: Please do not take offence if I request your solution to be "non-geeky".

      Vacc1 Vacc2 Vacc3
    pt001 DateVacc1 DateVacc2 DateVacc3
    pt002   DateVacc2  
    pt003   DateVacc2 DateVacc3

    • Post Points: 21
  • Tue, Jul 1 2014 8:25 PM In reply to

    • Rick Williams
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Melbourne, Australia
    • Posts 161
    • Points 2,382

    Idea [I] Re: Complex cross tabulation problem - Kindly help please

    A pivot table will work, so long as the data you want in the body of the table is numeric (numbers or dates). Your sample data (with text in the VaccDate field) won't work well with a PivotTable - you'll only be able to count the values, not display the text.

    I changed these for actual dates, and was able to do a demo pivot table, in the attached document.

     

    The alternative is to use a a 'helper column' which combines PtID and VaccName into a single value which uniquely identifies each row, which you can then look up using formulas in the output table.

    In my example, I've used =B6&"_"&C6 for the helper column, and

    =IFERROR(VLOOKUP($G18&"_"&H$17,$A$6:$D$11,4,FALSE),"No Date")

    for the formula in the body of the output table. Basically it looks at the row and column header for the current cell, sticks them together with a '_', and looks it up in the source data, returning a value from the fourth column (the 3rd column in your original table, as the first column is now the helper column). If it's not found (error) then I put in "No Date" to fill the cell. You could use any string you want, or even "" for blank cells.

    The last step is to format the output table with date formatting.

    I suspect that there is a third alternative that would involve 'PowerPivot' and composite keys, but I think that's a bit too 'geeky' for this application.

     

    See the attached workbook (just below the post heading) for my examples. Good luck!

    Rick

    Rick Idea
    Melbourne, Australia

    • Post Points: 5
  • Wed, Jul 2 2014 4:48 PM In reply to

    • Emess
    • Not Ranked
    • Joined on Tue, Jul 1 2014
    • Posts 2
    • Points 26

    Re: Complex cross tabulation problem - Kindly help please

    Brilliant! Thank you, Rick.

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.