merging columns

Sandeep123

I have a table with different viruses in different columns ( with rows description of detected/non detected)

 

I need them to be in one column with virus names in rows.

 

I tried to stack them. However, some patients have more than one virus. I would ideally want them to be in a columns like Virus 1 ( in majority of patients there would be only one virus), Virus 2 ( if there are two virus) , Virus 3 ( if there are three virus)

 

Thank you

 

txnelson

1. Stack all of the virus columns.  Make sure in the new table, the MR No remains on every row
2. I assume you then delete all rows that are marked "NON DETECTED"
3. Sort the data by Subject(MR No), so all of a subjects rows with Viruses detected are together
4. Create a new column which is a sequence number for each subject using this formula.  Call the new column "Virus#"

   If( Row() == 1 | :Subject != Lag( :Subject ),
   	:Virus# = 1,
   	:Virus# = Lag( :Virus# + 1 )
   )

5. Use Tables==>Split
   1. Split By Virus#
   2. Group by   MR No
   3.  Split Column  Label