Rearranging data from multiple rows with a common unique identifier to one row w

Question

Rearranging data from multiple rows with a common unique identifier to one row with all data associated with that unique identifier.

Leftmost column, Accession Nr., is a unique identifier and the top row for each Accession Nr. are demographics with no diagnoses. There are always 1+n rows for each Accession Nr. where n is the number of separate diagnoses for the sample.

I need to rearrange it from multiple rows with either demographics or one diagnosis each to one row with demographics followed by the associated diagnoses in columns.

I think this graphic explains better than I can.

Thanks for any help !!

Explanation / Answer

This is a manual solution: You can also use a macro - but that depends on how skilled you are.

I have assumed that your first blank column is H, and the data of interest is in columns F and G. IF that is not the case, then you need to adjust these instructions.

First off, make a copy of your sheet, and do this on the copy until you are sure you have done it correctly.

In H1, enter the formula

=F1

And copy to the right for 5 columns.

Then in H2, use the formula

=IF(F2="",F3,"")

and copy to I2.

In J2, use the formula

=IF(AND(F2="",F4<>""),F4,"")

and copy to K2.

In L2, use the formula

=IF(AND(F2="",F4<>"",F5<>""),F5,"")

and copy to M2.

Then copy H2:M2 down to match your rows of data. Finally, copy H:M and paste special values, and then delete columns F and G. Then select column B, use Ctrl-G choose the "Special..." button, select "Blanks" press "OK", right click the selection and choose Delete.... Entire row OK , and you are done.

If you ever have 4 rows of values for one ID, you need to use an extra 2 columns of formulas.

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.