How to tell excel to copy from one sheet to another with the condition?

I have an excel sheet which has around 4000 rows. Its about treatment details about the patients, where there are patients repeatedly came for the tests.

Now, wanted to create a new sheet which allows me to collect info from the old sheet.

conditions:

1. I need only one row represent a patient.

2. The second column onwards the info has be filled in the following way.

for example, if the data is entered in the following way:

 patient_id   test1   test2     test3
 001            1        0          1
 001            0        1          0
 .
 .
 .
 002            1         1          1
 002            0         0          0
 .
 .
 .
 003            1         0          0
 .
 .
 .

Now the in the new sheet, the first column should show the patient id and in the second column if she has alteast one 1 in her follow up(means if she has 1 in any of her visit in the test1) it should return 1 otherwise 0.

I dont know how many times a patient come for test.. its not uniform.

Similarly for the second and third column.

How to do that??

I hope patient name column also can be entered in the sameway.

If its not possible and can be done easily in other softwares..I wish to know that.

Thanks for helping!!!