How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)

In many cases, you may need to consolidate, merge or combine data. In Microsoft Excel, you can do such types of tasks in bulk and within seconds. This article demonstrates how to consolidate data in excel from multiple rows with some quick methods.


Download Practice Workbook

You can download the practice workbook from the link below.


4 Methods to Consolidate Data from Multiple Rows in Excel

Now, let’s assume you have a dataset with a list of Countries and their Cities. Here, you want to have the multiple rows for Cities consolidated beside their Country. At this point, I will show you two methods using this dataset to do so.

how to consolidate data dataset

1. Using UNIQUE and TEXTJOIN Functions

Using UNIQUE and TEXTJOIN functions is one of the fastest and most convenient ways to consolidate data from multiple rows in Excel. Now, follow the steps below to consolidate data using these functions.

Steps:

  • First, create a new column for Country beside your dataset.
  • Next, select cell E5 and insert the following formula.
=UNIQUE(B5:B13)

In this case, cell E5 is the first cell of the new column Country. Also, B5 and B13 are the first and last cells of the dataset column Country.

Moreover, we use the UNIQUE function. The syntax of this function is UNIQUE(array, [by_col], [exactly_once]).

Using UNIQUE and TEXTJOIN function

  • Then, add another column for consolidated data of the cities.
  • After that, click cell F5 and insert the following formula.
=TEXTJOIN(",",TRUE,IF(E5=B5:B13,C5:C13,""))

Here, cell F5 is the first cell of the new column City. Also, cells C5 and C13 are the first and last cells of the dataset column City respectively.

Moreover, here we use the TEXTJOIN function. The syntax of this function is TEXTJOIN(delimiter,ignore_empty,text1,…). Also, we use the IF function.

Using UNIQUE and TEXTJOIN function

  • Finally, drag the Fill Handle for the rest of the column.

Using UNIQUE and TEXTJOIN function


2. Applying IF Function and Sort

Another way to consolidate the data from multiple rows in excel is to use the IF function and the Sort option from the Data tab simultaneously. Now, follow the steps below to do so from the above dataset.

Steps:

  • First, select the cell range you want to sort. In this case, it is range B5:B13.
  • Then, go to the Data tab > Sort & Filter > Sort A to Z.

applying IF function and SORT to consolidate data in excel from multiple rows

  • Now, a Sort Warning box will pop up. At this point, select Expand the selection.
  • Next, click on OK.

applying IF function and SORT to consolidate data in excel from multiple rows

  • Consequently, add another column for Cities.
  • After that, select cell D5 and insert the following formula, and drag the Fill Handle for the remaining cells of the column.
=IF(B5=B4,D4 &","& C5,C5)

In this case, cell D5 is the first cell of the column Cities.

applying IF function and SORT in excel

  • At this point, insert a new column named Final Sort.
  • Then, select cell E5, insert the following formula and drag the Fill Handle for the remaining column cells.
=IF(B5<>B6,"Final Row","")

In this case, B5 and B6 are the first and second cells of the column City respectively. Also, E5 is the first cell of the column Final Row.

applying IF function and SORT to consolidate data in excel from multiple rows

  • Now, select and copy range D5:E13 and paste them in Values format to remove their formula.

applying IF function and SORT in excel

  • Next, go to the Data tab > Sort.

applying IF function and SORT in excel

  • At this point, from Sort by options select Final Sort.
  • Then, from the Order options select Z to A.
  • Consequently, click OK.

applying IF function and SORT to consolidate data in excel from multiple rows

  • Now, a Sort Warning box will pop up. At this point, select Expand the selection.
  • Next, click on OK.

applying IF function and SORT in excel

  • At this point, you will have your output as shown in the below screenshot.

applying IF function and SORT to consolidate data in excel from multiple rows

  • Finally, delete all extra rows and columns and have your desired output.

applying IF function and SORT to consolidate data in excel from multiple rows


3. Utilizing Consolidate Option to Consolidate Data from Multiple Rows in Excel

Now, suppose you have a dataset where you have sales made by a few persons on different occasions. At this point, you want to consolidate the data of their sales and get their sum from multiple rows. You can follow the steps below if you want to do so.

Steps:

  • First, select the cell you want your new data in.
  • Second, go to the Data tab.
  • Then, select Consolidate from the Data Tools.

utilizing consolidate option to consolidate data from multiple rows

  • Then, select Sum from Function options.
  • After that, select the Reference, In this case, it is $B$5:$C$14.

Here, cell B5 is the first cell of the column Sales Person and cell C14 is the last cell of the column Sales Amount.

  • Next, pick the Left column from Use labels in.
  • Consequently, click on the OK button.

utilizing consolidate option to consolidate data from multiple rows

  • Finally, you have your consolidated data for sales.

utilizing consolidate option to consolidate data from multiple rows

Note: If you want to get your data consolidated based on criteria, first Sort your data according to your criteria and then use the Consolidate option.


4. Applying VBA Code to Consolidate Data from Multiple Rows in Excel

Also, you can apply VBA code to easily consolidate data from multiple rows in Excel. If you want to do so, you can follow the steps below.

Steps:

  • First, press ALT + F11 to open the VBA window.
  • Now, select Sheet 7 or the sheet you are working on and Right-Click on it.
  • Next, sequentially select Insert > Module.

Applying VBA code

  • At this point, copy the following code and paste it into the blank space.
'This code will consolidate data from multiple rows
Sub ConsolidateMultiRows()
'Declares Variables
Dim Rng As Range
Dim Dat As Variant
Dim j As Variant
'Creates a InputBox to insert Reference Cell Range
On Error Resume Next
Set Rng = Application.Selection
Set Rng = Application.InputBox("Range", "Enter Your Reference Range", Rng.Address, Type:=8)
'Runs a For loop add all the data for same Sales Person and Updates Screen
Set Dat = CreateObject("Scripting.Dictionary")
j = Rng.Value
For i = 1 To UBound(j, 1)
    Dat(j(i, 1)) = Dat(j(i, 1)) + j(i, 2)
Next
Application.ScreenUpdating = False
'Clears all the extra contents and updates screen
Rng.ClearContents
Rng.Range("A1").Resize(Dat.Count, 1) = Application.WorksheetFunction.Transpose(Dat.keys)
Rng.Range("B1").Resize(Dat.Count, 1) = Application.WorksheetFunction.Transpose(Dat.items)
Application.ScreenUpdating = True
End Sub

Applying VBA code

💡 Code Explanation:

In this part, I will explain the VBA code used above. Now, I have divided the code into various sections and numbered them. At this point, I will explain the code section wise.

  • Section 1: In this section, we create a new Sub named ConsolidateMultiRows().
  • Section 2: Next, we declare different variables.
  • Section 3: Here, in this section, we create an InputBox that will ask for our reference range.
  • Section 4: We run a For loop for adding the Sales Amount.
  • Section 5: Finally, we need to clear all the extra contents and rearrange the cells.

Applying VBA code

  • Now, press F5 and run the code.
  • At this point, a box will appear like the screenshot below.
  • Next, insert your reference range
  • Finally, click the OK button.

Applying VBA code

  • Lastly, you have your consolidated data like the screenshot below.

Applying VBA code


Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo