Merging rows according to ID and date/time period

[shoren]

Hey. I'm working with a very large dataset for a campus health center, where the same individuals came in over the course of several years and completed the same 3 surveys each time. I'm using Excel 2010 on Windows 7.

Basically I need the three variables' data merged into one row associated with that id number. .. That would make it a "set" (having intake, treatment, and FU). That's easy.

BUT I want to treat each id (if there are multiple independent sets from the same id) as its own set, taking into account the date range from their surveys.

Is this something that could be accomplished by computing a day count by recency (how many days since today) and then computing how big that count is relative to the other counts for that specific ID .. And then splitting the cases based on how big that count is? I'm not sure how to approach this.

I hope I've been clear. Just to reiterate: I want to create a row 'case' (via merging the rows) based on their client id AND the date range associated with their data. I'd really appreciate any help, tips or links, thanks. This is really nothing they talked about in my master's program.

<table border="0" cellpadding="0" cellspacing="0" width="564"><colgroup><col style="mso-width-source:userset;mso-width-alt:3803; width:78pt" span="2" width="104"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:78pt" height="20" width="104">Client ID</td> <td style="width:78pt" width="104">Date Collected</td> <td style="width:86pt" width="115">Type</td> <td style="width:58pt" width="77">Intake Data</td> <td style="width:75pt" width="100">Trment Data</td> <td style="width:48pt" width="64">FU Data</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">12/30/2011</td> <td>Intake</td> <td align="right">12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">12/31/2011</td> <td>Treatment</td> <td>
</td> <td align="right">55</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">1/1/2012</td> <td>Follow-up</td> <td>
</td> <td>
</td> <td align="right">32</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">1/5/2011</td> <td>Intake</td> <td align="right">44</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">1/8/2011</td> <td>Treatment</td> <td>
</td> <td align="right">24</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">1/9/2012</td> <td>Follow-up</td> <td>
</td> <td>
</td> <td align="right">54</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">5/5/2005</td> <td>Intake</td> <td align="right">33</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">5/6/2005</td> <td>Treatment</td> <td>
</td> <td align="right">52</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">3</td> <td class="xl65" align="right">5/7/2005</td> <td>Follow-up</td> <td>
</td> <td>
</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">7/30/2001</td> <td>Intake</td> <td align="right">11</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">7/31/2001</td> <td>Treatment</td> <td>
</td> <td align="right">4</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="right" height="20">2</td> <td class="xl65" align="right">8/1/2001</td> <td>Follow-up</td> <td>
</td> <td>
</td> <td align="right">5</td> </tr> </tbody></table>

 

[Smitty]

A Pivot Table might do what you want.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Client ID</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Row Labels</td><td style=";">Max of Intake Data</td><td style=";">Max of Trment Data</td><td style=";">Max of FU Data</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">7/30/2001</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">7/31/2001</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">8/1/2001</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">12/30/2011</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">12/31/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">55</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">1/1/2012</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />

HTH,

 

[shoren]

Thanks for your reply. Can Pivot tables reorganize the data in the worksheet so that the variables will be merged into a single row(case)? I'm not clear on that.

I need to put the data in a form for importing into SPSS - I don't only need a summary.

 

[Smitty]

No, Pivot Tables won't pull the data into one row unless you remove the dates.