How to Combine Cells with Same Value in Excel (3 Easy Ways)

Excel is one of the most widely used tools where we work with datasets. We often need Excel to combine cells with the same value. In this article, I will explain how to combine cells with the same value in Excel.


Download Practice Workbook

This is the dataset I am going to use to explain the methods. Here, we have some salespersons and the list of Products that they have sold. I will combine the same values.

excel combine cells with same value


3 Methods to Combine Cells with Same Value in Excel

1. Using IF & CONCATENATE Functions in Excel to Combine Cells with Same Value

First, I will show you how to combine cells with the same value using the IF and the CONCATENATE functions together.

STEP-1: CREATING AN INTERMEDIATE COLUMN

First, I need to create an intermediate column where all the items will be listed.

Then go to D5. write down the following formula

=IF(B5=B4,D4&","&C5,C5)

excel combine cells with same value

Here, in the IF function the logical statement is B5=B4, if it is  TRUE it will return D4&”,”&C5 (which eventually is Intermediate Column, Laptop), and if FALSE, it will give C5 as output. Since the statement is FALSE, we have C5 as output.

Then press ENTER. Excel will return the output.

After that, use the Fill Handle to AutoFill up to D14.

excel combine cells with same value

STEP-2: CREATING THE LIST

To create the Final List, I will use the combination of IF and CONCATENATE functions.
➤ Create a new column, “Final List”.
➤ Go to E5 and write down the formula

=IF(B5<>B6,CONCATENATE(B5," ","sold"," ",D5),"")

Formula Breakdown:

“ “ —> It creates space.

  • CONCATENATE(B5,” “,”sold”,” “,D5) —> Concatenates the words or cells.
    • Output: Alex Morgan sold Laptop

IF(B5<>B6,CONCATENATE(B5,” “,”sold”,” “,D5),””) —> Returns the output after analyzing the logical statement B5<>B6.

  • IF(FALSE,{Alex Morgan sold Laptop},{})
    • Output:{}

➤ Now press ENTER. Excel will return the output.

excel combine cells with same value

Then, use the Fill Handle to AutoFill up to E14.

➤ Now select the entire dataset.

➤ Then go to the Data tab >> select Sort & Filter >> select Filter.

excel combine cells with same value

Then select the drop-down (see image).

➤ After that, uncheck the Blanks option and click OK.

excel combine cells with same value

You will get the list with the same values.

NOTE:

In this method, you must remember that the same values should be next to each other. For instance, I have sorted the dataset in a way that the cells having Alex Morgan are adjacent to each other.

Read More: How to Combine Cells Using Excel Formula (6 Methods)


2. Employing Consolidate Feature to Combine Cells with Same Value in Excel

Now I will show how to use the Consolidate feature to combine cells with the same value. To perform this method, I have added the Selling Price column.

excel combine cells with same valueSTEPS:

Select F4. Then, go to the Data tab >> Data tools >> select Consolidate.

A Consolidate dialog box will pop up. Set the function Sum as you are going to sum the same values. Then, set the reference. The entire table B4:D14 is my range here.
Click Add.

excel combine cells with same value

➤ Excel will add the reference. Then mark the Left column and click OK.

➤ Excel will combine the same values and return the sums.

excel combine cells with same value

Now format as you wish.


3. Applying VBA to Combine Cells with Same Value

Now, I will apply VBA to list out the same values in a dataset.

STEPS:

➤ Press ALT + F11 to open the VBA window.
VBA window will open. Then go to Insert >> Module

excel combine cells with same value

➤ Type the following code in the Module.

Sub CombineCells()
    Dim Col As New Collection
    Dim Sr As Variant
    Dim Rs() As Variant
    Dim M As Long
    Dim N As Long
    Dim Rg As Range
    Sr = Range("B4", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)

    Set Rg = Range("E4")
    On Error Resume Next
    For M = 2 To UBound(Sr)
        Col.Add Sr(M, 1), TypeName(Sr(M, 1)) & CStr(Sr(M, 1))
    Next M

    On Error GoTo 0
    ReDim Rs(1 To Col.Count + 1, 1 To 2)

    Rs(1, 1) = "Name"
    Rs(1, 2) = "Products"
    For M = 1 To Col.Count
        Rs(M + 1, 1) = Col(M)
        For N = 2 To UBound(Sr)
            If Sr(N, 1) = Rs(M + 1, 1) Then
                Rs(M + 1, 2) = Rs(M + 1, 2) & ", " & Sr(N, 2)
            End If
        Next N
        Rs(M + 1, 2) = Mid(Rs(M + 1, 2), 2)
    Next M

    Set Rg = Rg.Resize(UBound(Rs, 1), UBound(Rs, 2))
    Rg.NumberFormat = "@"
    Rg = Rs
    Rg.EntireColumn.AutoFit
End Sub

Here I have created a Sub Procedure CombineCells. Then with the dim statement, I have declared Col, Sr, Rs, M, N, Rg as variables.
The Rg variable is set at E4 which indicates that the result will be displayed at E4.
Then, I have used a For loop to list out the products. I used the Ubound function with Rs as arrayname.

➤ Then press F5 to run the program. Excel will combine the names.

excel combine cells with same value

Then you can format it as you want.

Read More: How to Combine Cells into One with Line Break in Excel (5 Methods)


Practice Workbook

Practice makes a man perfect. That’s why I have attached a practice sheet for you.


Conclusion

In this article, I have explained 3 ways in Excel to combine cells with the same value. I hope you will find these helpful. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

2 Comments
  1. Akib,

    I am a neophyte in Excel. I have column A as Ideas then Columns 2 and above I have the category they belong.
    Idea/Baby/Infant/Teeen/Young Adult/Adult/etc.

    I have X’s in columns B-L. I am listing ideas randomly and wanted to know if there is an easy way to sort by each columns so all my Baby’s/Teen’s etc are together and the ideas are attached to them. Probably easy just not sure how to do it.

    Thanks for any pointers or links.

    • Reply
      Fahim Shahriyar Dipto Nov 7, 2022 at 5:13 PM

      Hello Mr. Clark. Thanks for your valuable comment. In your query, you wanted to know about category-wise idea sorting. If I misunderstood your problem then please let me know about this. I am adding the sorted image here. Then I have described how I have done that.

      Firstly, you need to set a list of ideas according to your category just like the image below.

      Then in cell B5 write up the following formula.
      =IF($B$4=J5,I5,"")

      Similarly, you have to put the same formula for the other columns but need to set the cell reference for the corresponding cells.
      Then drag down the Fill handle Tool for C5, D5, E5 ad F5 cells to get the same formula.

      Finally, you will get the result.

      Now, if you want to add any category-wise idea then it will automatically be sorted in your dataset list. See the image for a better clarification.

Leave a reply

ExcelDemy
Logo