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

Question

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.

Dr. Otter

Answer 1

Dr. Otter,

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.

You can also use a macro - but that depends on how skilled you are.

Answer 2

Thank you, Bernie!

It worked perfectly.  I also learned some tricks I could use in the future.  I really appreciate the help!

Dr. Otter

Answer 3

Excel 2010 with free Power Query Add-In.

Compatible with Office 2013 Pro Plus.

No formulas, no VBA macro needed.

With position independent Tables.

No $A$1 or R1C1 cell references.

Extraordinary skills needed anyway.

http://www.mediafire.com/view/z9ip7dnq9er5snm/02_10_15.xlsx

http://www.mediafire.com/view/4br1v6rluvc52l7/02_10_15.pdf