Search code examples
excelspss

How to mass copy rows depending on a counter in either SPSS or excel

The problem I have is as such:

I have a database full of xrays and associated information for a long list of patients, ordered by patient ID. What I want to do is to find the time of the first xray performed for each patient, and I want to fill this in for each xray of these patients. The issue is that each patient often has multiple xrays, and I'm not sure how to copy the data from another column into my timeOfFirstXray column.

For example; (sorry for my very poor formatting)

ptid ...dateandtimeofxray .....dateandtimeof1stXRAY

4 .......21/1/2011 4:30 ............21/1/2011 4:30

4 .......22/2/2011 5:11 ...........21/1/2011 4:30

4 .......25/5/2011 5:00 ........... 21/1/2011 4:30

5 .......29/7/2000 3:00 ............ 29/7/2000 3:00

5 .........30/7/2000 4:00 ......... 29/7/2000 3:00

5 ........ 31/7/2000 5:00 ......... 29/7/2000 3:00

5 .........1/8/2000 1:00 ........... 29/7/2000 3:00

6 .........1/9/2002 2:00 ........... 1/9/2002 2:00

8 ........ 3/10/2008 3:23 ......... 3/10/2008 3:23

8 .........4/10/2008 5:00 ......... 3/10/2008 3:23

8 ........ 5/10/2008 9:00 ......... 3/10/2008 3:23

My psuedocode is:

Set a counter to 0 initialise date variable to first row's date value foreach row, if counter < patientID, counter++ until equal with id, set date variable to value of date in this row, and set this value to timeOfFirstXray for the row. else if counter == patientID, copy existing value in date variable into timeOfFirstXray for this row

I've never used SPSS or excel for this type of stuff before, so basically what I'm looking for is the above translated.

Thankyou for reading!

Solution

  • Here's an Excel option. Assuming your data starts in row 2, enter this formula in C2 and copy down. Note that this is an array formula and must be entered with Ctrl-Shft-Enter. (If done correctly you'll see curly braces around the formula.) Then just copy down as far as needed:

    =MIN(IF(($A$2:$A$12=A2)*($B$2:$B$12),$B$2:$B$12))

    enter image description here

    Adjust your ranges to fit you data. Note that it gets the earliest date even if they're not in chronological order in your source data.

    If you are interested in the logic behind the formula, see this post on my blog.

    If you wanted to just show the minimums, you could accomplish something similar to Andy W's comment above by using a pivot table in Excel (Excel 2010 shown here). The key is to "show the values as" Min:

    enter image description here