Translating a list of patients with multiple lines to one line each with all data across the page|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi,

I'm new here and am having trouble seeing if this question has been raised before.  I'm having trouble even describing what I am trying to do.

I am wanting to find a way to take a table which lists patients and every diagnosis they have with one diagnosis per line meaning that some patients have lots and lots of lines and transforming it to one line per patient with all the diagnoses listed from left to right after the patient details in the appropriate order for the diagnosis.

I have attached an example of what I mean.  The table on the left is the starting data with the two grey columns either side added to enable me to transform the data into the table on the right. The initial data is first sorted into the correct order for the diagnoses for each patient and the Order column is added in indicate the correct order.  the concat() column is then added at the far left of the table to enable a vlookup to work in the second table.

All suggestions gratefully accepted 🙂

Thanks you

Leeanne