Comments on: How to merge rows in Excel without losing data

This article provides solutions for 4 different scenarios of merging rows in Excel. See how you can quickly merge multiple rows without losing data; how to combine duplicate Excel rows, how to repeatedly merge blocks of rows, and how to consolidate matching rows from two different spreadsheets. Continue reading →

Comments page 3. Total comments: 118

  1. Pls suggest how do I concatenate for 5000 cells ?

  2. I have 3 rows as AB0U-RFW-010-9012-01 AB0U-RFW-010-9012-02, AB0U-RFW-010-9012-03, AB0U-RFW-010-9012-04

    i want only one entry to show AB0U-RFW-010-9012-01~04 can you help how to getthis

  3. how i can change the data 03 or 4 row to one

  4. Hi plz help me for moving data to other file without loss

  5. Bonjour! I hope you will be able to help me! I have an Excel sheet containing column A, listing natural medicinal substances. Then columns B to AA have headers indicating the possible medicinal uses of said substances. There are 26 uses and I put "X"s in the column(s) that correspond to each substance's uses.
    To make a long story short, at first I had one excel spreadsheet for each use of my substances, and I now want to combine all the data in one larger spreadsheet. BUT, since each substance has multiple uses, I end up with, for example, 4 rows for "Tansy" (Ex. A 21, 22, 23, 24), and in each row, an X appears in the column corresponding to one use of the herb (Ex:B:21, C:22, J:23 and Y:24). What I would like to do is to combine the 4 Tansy rows into one (ex. row 21), where I would have B:32, C:21, J:21 and Y:21.
    I hope I managed to be clear, and I thank you very much in advance for any help you could provide in letting me know is your Wizards could solve my problem! IM

    1. Hello Isabelle,
      It sounds like you can use Combine Rows Wizard for your task:
      - Select all your data on step 1 and choose to create a backup copy just in case;
      - Select column A with the substances on step 2;
      - Use the top check-box to select all columns with the uses on step 3, and select the options to "Delete duplicate values" and "Skip empty cells". As you have the same "X"s in all cells, it doesn't matter what delimiter you leave in the field.
      - Click Finish
      If you have any difficulties, please send a sample worksheet to support@ablebits.com, we'll do our best to assist you.

  6. Can anyone help me out on how to repeatedly merge blocks of uneven rows into one row.

    I have very large data with uneven blocks of row.

    I have something like the table below
    Name Reported Date+"" Summary*"" Notes""
    Istifanus 5/4/2015 8:08 2-IT Incident Report - Hardware - Dear Team,
    abcd
    efgh
    kjil
    Jane 5/4/2015 8:17 2-IT Incident Report - Hardware - Good morning,
    port
    Newman
    Anderson
    Caleb 5/4/2015 8:27 2-IT Incident Report - Hardware - Stevedores

    I want to merge the Notes into one cell against the other contents of the first row.

    Thank you for your help

  7. Dear All, Am trying to merge multiple rows in a particular column without affecting the contents of other columns.

    I'll appreciate if anyone have an idea on how to go about this

  8. Firstly, great article and very well explained!

    Secondly, I receive client databases in excel files. However, while we want to receive the data like this:

    Column 1 Column 2 Column 3 Column 4
    John Smith 10 Smith St John@gmail.com

    Instead we get the data like this:

    Column 1 Column 2 Column 3 Column 4
    John Smith 10 Smith St John@gmail.com Nothing Nothing Nothing

    or this:

    Column 1 Column 2 Column 3 Column 4
    John,Smith,10 Smith St,John@gmail.com Nothing Nothing Nothing

    Is there a way to write up a script or some way to reformat the data to get the format we want - which is the first example?

  9. I am trying to bring an output over to another cell for a schedule that I am creating my worksheet is broken down into 15 minute intervals and I am trying to get the lunch to cover an hour

    my current formula is as follows
    =IF($B9>G$1,"FREE",IF($F9<G$1,"FREE",IF($C9=G$1,"BREAK",IF($D9=G$1,"lunch",IF($E9=G$1,"BREAK","DL")))))

    I can only get the if summary to output lunch to that one 15 minute cell does anyone know how I can add it to multiple cells without changing my data table or how I can merge multiple cells with my formula

    1. Hello Andrew,

      I am sorry, it is difficult to recommend you something without seeing your data. For us to be able to assist you better, please post a small sample workbook with your data on our forums. Our support team will try to help.

      1. okay I will do that.. but basically what I would like to do is in my formula tell lunch to consume 4 cells instead of just one by copying over 4 times without creating an endless cycle or tell the the "lunch" to merge multiple cells when during that time slot

  10. I have an inventory report that shows the cost when we purchased items. It first lists the item number and description, and then on another line it shows the date, qty, cost. Some items have just one purchase date, ie one row, and others have up to 5 rows. The items with multiple rows have a subtotal, the ones with one row do not. I want to get the part number and description on the same line(s) as the purchase date information. How do I do that?

    More detail:
    cell A2 and B2 have the item# and description.
    Cell B3 C3, D3, E3 have the purchase date, item cost, and qty. and extended cost.
    But sometimes there will also be a Cell B4, C4, D4 and E4 with another purchase date, cost and qty. And then this would be followed with a subtotal of the qty and cost in row 5.
    I need to get the Item# and Description to show up on all the lines for that part.

    HELP!???

    1. Hello Tommy,

      I am sorry, but there is no way to fulfill your task using formulas. If your data are more or less sorted, a special VBA macro should help. You can ask for it on http://www.mrexcel.com or http://www.excelforum.com

      If your data are distorted, you’d better hire freelancers who sort your data manually or write a macro.

  11. Hi
    How do you link an excel populated data sheet(A) to another data sheet(B) but manage to keep the information lock to dates when data is combined in data sheet(B).
    Thank You

  12. I have an online order system that gives me an excel sheet of individual items placed in an order. I want to merge the items and quantity based on each order. Is this possible with your program?

    Example:

    Order Item Quantity
    100 Plates 500
    100 Shoes 2
    101 Phones 1
    101 Paper 3

    I would like it to look like this (or similar):

    Order Item
    100 Plates - 100, Shoes - 2
    101 Phones - 1, Paper - 3

  13. I tried INT((ROW(C2)-2/3)) to combine multiple rows in my spreadsheet and I did get a value of 1 for C2, but I cannot find the block id or combine row wizard in Excel 2007.

    Thanks,
    Shana

  14. I have a very lengthy spreadsheet, and I need to sort everything into alphabetical order by name in column "A", but each cell in column "A" has specific data in columns "B" thru "G" linked to it. Is there a way I can sort alphabetically without mixing up all the other data. My only thought was to merge each row entirely, and then sort via alphabetical order than un-merge all rows. Is there an easier way?

    1. Nevermind.

  15. Am not sure what I need is covered? I have two equal length adjacent columns, both containing text. I need to merge the text in column 1 with the text in column 2, into a single column (say 3) with no loss of data.
    For example,
    1 2 3
    A X A,X
    B Y BECOMES B,Y
    C Z C,Z
    . . .
    . . .

    1. Hello Peter,

      Here's the formula:

      =IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1+B1,A1&","&B1)

      1. Spot on!

        Thanks,

        P

  16. Hello Svetlana,
    Is there any formula to merge multiple empty cells in a column?

    Thank you

  17. Hi,
    I want to merge the rows. but in the content some of the rows are blanks. i tried the concatenate formula like this(=A1&","A2) . Its not working. Tell me how to merge the rows with comma if there are blank rows inbetween the content.

    1. Hello Mike,

      Sorry, I do not exactly understand the task. If you can send a sample workbook with your data to support@ablebits.com and include the result you want to get, our support team will try to help.

  18. Hi,
    Is there a way to use the combine rows and have the program actually write out all the columns instead of entering data in a column separated by semicolons or commas? I want the duplicate data actually expanded across columns:
    Sample
    A B 32 25 Y N
    A C 32 22 N Y
    Combined
    A B C 32 32 35 22 Y N N Y

    I love the program, it is fantastic but I need to expand the results and not sure how to do that.

    Thanks,

    Gene

    1. Hello Gene,

      If you have simple data (without commas), the following solution might work for you:

      1 Run Combine Rows Wizard to merge the data and choose a semicolon as the separator.

      2 Save the resulting worksheet as a .csv file. For more details, please see this article:
      https://www.ablebits.com/office-addins-blog/convert-excel-csv/

      3 Open the .csv file in Notepad and replace all semicolons with commas. Save the file.

      4 Open the .csv file in Excel, see here for full details:
      https://www.ablebits.com/office-addins-blog/convert-csv-excel/

  19. Hi Svetlana,

    Hope you are doing good.
    Quick question.

    Lets say I have:

    firstname lastname email

    abc dec test@gmail.com
    123 456 123456@gmail.com

    When filling in the data, at times, the email is firstnamelastname@<email.com.
    I would like a way that when this is the case, I use a shortcut and firstnamelastname is automatically pasted in the 3rd column.
    Now this has to be a shortcut cause not always, this is the case.

    I hope you understood my query. Is this possible in any manner?

    Kind Regards,
    Hem

    1. Hi Hem,

      If my understanding is correct, it is not possible to fulfill your task using formulas. Most likely you need a small VBA macro.

  20. Hi there, how to I undo the merged cells?

    1. Hello Sumen,

      It depends on how the cells were merged. If you have just merged them, you can use the standard Excel Undo feature (Ctrl+Z). Our merging add-ins create a backup copy of your worksheet before merging the data. In other cases, I'm afraid, Undo is not possible.

  21. I am having the list as mentioned below and i want the result which is mentioned.
    please help by provinding the solution as mentioned in name coloum is change day by day i expecting the formula from where he automaticaly give me the result name of the customer which is present in name column.

    Name code amount
    Abc 123 2
    DEF 456 3
    GHF 458 4
    KLM 741 5
    NMR 351 6
    DEF 456 7
    KLM 741 8
    MNP 3425 9
    NMR 351 10
    MNP 3425 11
    JKL 7538 12
    Abc 123 13
    DEF 456 14
    GHF 458 15
    GHF 458 16
    DEF 456 17
    DEN 753 18

    I want the Result to be like mentioned below by using the formaulas

    Name Code Amount
    Abc 123 15
    GHF 458 35
    DEF 456 41
    KLM 741 13
    NMR 351 16
    MNP 3425 20
    JKL 7538 12
    DEN 753 18

    1. I believe the following solutions will work:

      Solution 1:
      - Sort your table by the Name column.
      - Go to Data > Outline and click on Subtotal.
      - Specify the following settings:
      At each change in: Name
      Use function: Sum
      Add subtotal to: Amount
      - Click OK and then press Number 2 in the upper left-hand corner of your worksheet to get the sum by name.
      For more information about using subtotals in Microsoft Excel, please see:
      https://www.ablebits.com/office-addins-blog/subtotal-excel-insert-use-remove/

      Solution 2:
      - Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
      - Choose to insert the PivotTable report onto a new worksheet and click OK.
      - In the PivotTable Fields pane, drag and drop the Names column to the Rows section and the Amount column to the Values section.
      You will get the same result.

      Solution 3:
      - Copy your table to another worksheet
      - Go to Data > Remove duplicates to delete duplicated names.
      - Type the following formula in the Amount column:
      =SUMIF('V3 - source'!A2:A18,A2,'V3 - source'!C2:C18)
      Where 'V3 - source'!A2:A18 is the reference to the Names column of your source data, 'V3 - source'!C2:C18 – the reference to the Amount column.

      You can download the workbook with all 3 solutions using the below link:
      https://cdn.ablebits.com/_img-blog/_comments/jhon%20-%20119.235.57.75%20-%20sumif.xlsx

  22. I am having a 5000 customer in Column "A" and In column "A' most of the customer is repeated Now i want the formulas from which i will check the names of the customer which is present in this column.

    1. Hello John,

      There exist a number of solutions depending on what exactly result you are trying to achieve.

      If you want to highlight all duplicates in a column, please see this article:
      https://www.ablebits.com/office-addins-blog/how-to-highlight-duplicates-excel/

      To highlight duplicate entries between 2 columns, see the "Compare 2 columns for duplicates" example on this page:
      https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

      If you want to merge the duplicate rows into one, check out the Combine Duplicated Rows add-in:
      https://www.ablebits.com/excel-combine-rows/index.php

  23. Thank you so much for this!! I was pulling my hair out trying to merge address data into one cell so that it would be more usable in a Mail merge in Microsoft Office. You saved my day!!! Thank you, thank you, thank you!!!

  24. let's say I have 2 rows of the exact same data, except for ONE cell in each column.

    For an example, let's make the headers: a. Customer Name, b. Customer ID, c. Customer Address, and d. Amount Paid.

    Now rows 1 & 2 are the exact same customer, except in row 1 column d the dollar amount is $500, and in row 2 the dollar amount is $300.

    Is there a way to quickly consolidate into a single line, with the dollar amount in column d the sum of the previous entries? (i.e. $800)?

    1. Hello Harold,

      You can do this in 2 ways:

      1. Using Subtotal. Sort your table by Customer ID and then apply subtotal in this way: At each change in "Customer ID", use function "Sum", add subtotal to column "Amount Paid". Please see this article for more details: Using Subtotals in Microsoft Excel

      2. Using a pivot table.

  25. Hi Swetlana
    I can not find the menu and i want to help you on urgent basses so kindly help me.

    Regards
    Mukesh Walia

    1. Hello Mukesh,

      I am sorry, I do not exactly understand the problem. Do you mean you do not see the add-in's menu in Excel after installation? Anyway, you can reach our support team at support@ablebits.com and we will promptly respond and fix the issue.

  26. I tried out this merging tool for the past two days and I like it a lot, but I run into problems, as soon as I try to combine a sheet that just contains text and number data with a sheet that has a date column. Is there something that has to be considered when doing this? I tried every possible combination, but it just does not seem, that my merged values are ever correct...

    1. Hello Bridget,

      Thank you for your interest in our merging add-in for Excel, I am really glad to know you like it!

      I'm afraid we cannot determine the exact cause of the problem without seeing your data. Can you please send me your workbook (or just a sample of data) at support@ablebits.com and describe the result you are trying to achieve? I will do my best to help.

  27. Can I use this tool if I need to use the contents 2 columns ?

    1. Hi Gayle,

      Of course, you can use one of the tools described in the article, Merge Cells add-in, to combine the contents of 2 columns. You can find the detailed description with screenshots here.

  28. Hey Friend If u are still here so please tell me how to Merge or combine two column without losing data...like 1 have numbers in A1 and AW1,AX1 both had merged.i want copy A1 number to AW1,AX1...i Have a Very Big Spreadsheet..is it Possible or i ahve to do Manually...please Reply Fast..Thanks...

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)