+ Reply to Thread
Results 1 to 9 of 9

Merging rows without removing data

  1. #1
    Registered User
    Join Date
    04-24-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Lightbulb 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.
    Attached Files Attached Files
    Last edited by harr15; 04-24-2019 at 11:05 AM.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,314

    Re: Merging rows without removing data

    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.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-24-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging rows without removing data

    Thank you for your helpful advice Alan - I have adjusted my question accordingly and uploaded a file.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Merging rows without removing data

    I'm going to assume that this is a "one-off" reorganization and not something that has to be repeated on a regular basis.

    Here's the steps with reference to your example workbook.
    1. Copy/paste patient IDs (excluding the header row) into col-B below existing data, say B28.
    2. With the patient IDs that you just copied still selected then on the ribbon: Data > Remove duplicates. Make sure "My data has headers" is unchecked, then "OK"
    3. Enter the following formula into C28. It is an array formula and must be committed with CTRL-SHIFT-ENTER
    4. Formula: copy to clipboard
      Please Login or Register  to view this content.
    5. Select C28 then Copy
    6. Select D28:G28 then Paste-formulas
    7. Select C28:G28 then Copy
    8. Select C29:G33 then Paste
    9. Select a row of your Table-1 (say B17:G17) then copy
    10. Select B28:G33 then paste-formatting in order to get dates to display properly
    11. Finally, select B28:G33 and copy/paste-values.

    Obviously you will need to adjust the ranges in the formula for you real data. The one thing that's less than clear is the "3" in occurrences of "ROW($4:$17)-3". The "3" is one less than the start row (excluding headers), so that if your data (excluding headers) is in rows 7:1000 then this fragment of the formula would become ROW($7:$1000)-6

    The attached workbook implements the above steps stopping short of the final "paste-values" step.

    It's not as complicated as it sounds! Hopefully this helps - let me know.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-24-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging rows without removing data

    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!
    Last edited by AliGW; 04-25-2019 at 08:14 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,648

    Re: Merging rows without removing data

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Merging rows without removing data

    OK, sorry, my mistake. Actually, you would have seen further problems if D4 and E4 were not blank.

    In step-3 above please change the C28 formula (it simplifies a bit actually) to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It's still an array formula so enter using CTRL-SHIFT-ENTER

    Then follow the post #4 steps again.

    Let me know if this works for you.

  8. #8
    Registered User
    Join Date
    04-24-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Merging rows without removing data

    Geoff you are a star! Thank you so much for all of your help - I can't thank you enough.

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Merging rows without removing data

    No problem. Thanks for the feedback and reputation points

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. merging data from different rows
    By jyl7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2013, 12:22 PM
  2. Replies: 13
    Last Post: 02-12-2013, 12:49 PM
  3. Removing deplicate record of a participant after merging data
    By ajang in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2012, 01:07 PM
  4. [SOLVED] Merging several rows of data into one row.
    By Lozhaze in forum Excel General
    Replies: 9
    Last Post: 07-04-2012, 10:33 AM
  5. Merging to Rows of Data
    By RKCastillo in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 06-13-2012, 06:48 AM
  6. Removing Rows With Identical Data and Merging Cells
    By Burnout in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2011, 01:07 PM
  7. Merging rows of data and insertin rows conditionally
    By classixuk in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-28-2010, 07:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1