Merging rows according to ID and date/time period

shoren

New Member
Joined
Feb 18, 2012
Messages
2
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>
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Feb 18, 2012
Messages
2
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

Legend
Joined
May 15, 2003
Messages
29,536
No, Pivot Tables won't pull the data into one row unless you remove the dates.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,253
Messages
5,769,051
Members
425,512
Latest member
wannabe_excel_wiz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top