Merging rows without removing data

Hi everyone. I have a large database with a lot of duplicates - however each duplicate contains a different piece of information e.g. one row contains the type of diabetes, another contains date of presentation but for the same patient (same patient number). I would like to merge these rows so that I have one row which includes all of the information for that one person and all of their data is combined.

At the moment I am doing it manually but there must be an easier way as I have over 2500 rows in the real dataset (this is just an example with completely fictional information - not real patients). Thank you in advance for your help.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hi Geoff - thank you so much for your help!
I have applied your formula to my large dataset after trialling it on a smaller scale. However I have encountered one problem -
In the "Gender" and "Sexual orientation" columns, if there is blank data, the formula correctly leaves the cell blank.
However in the "Type of diabetes" column, if that patient has missing data e.g. it is left blank instead of saying if they have type 1 or type 2, it automatically fills in the cell with a 2. Therefore it skews my data. I was wondering if you could help with this or had any ideas. Thank you!

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.