## Merge matching rows in excel

**Question:**

I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there prizes daily (1 week). take note that in col. A fruits name may randomly repeated in col A. What I need is put in Sheet 2 col A all fruit name but not repeated and put to column B to H, I to N, O to U there prizes .see sample below. hope u understand.

A B C .... I

1 apple 10 11 .... 8

2 orange 9 9 ..... 10

3 apple 11 11 ..... 12

4 apple 14 10 ..... 10

5 grapes 15 15 ..... 14

In sheet 2 answer should be like this.

A B C ..... H I J.....N O P.... U

1 apple 10 11 8 11 11 12 14 10 10

2 orange 9 9 10

3 grapes 15 15 14

**Answer:**

Sheet 1:

Sheet 2:

**Array formula in cell A2:**

Copy cell A2 and paste down as far as needed. See this blog post for an explanation: How to extract a unique distinct list from a column

**Array formula in cell B2:**

Copy cell B2 and paste B2:K4. Read more about relative and absolute cell references.

**Explaining array formula in cell B2**

=SMALL(IF($A2=Sheet1!$A$2:$A$6, Sheet1!$B$2:$D$6, ""), COLUMN(A1))

*Step 1 - Filter values in matching rows*

=SMALL(**IF($A2=Sheet1!$A$2:$A$6, Sheet1!$B$2:$D$6, "")**, COLUMN(A1))

IF($A2=Sheet1!$A$2:$A$6, Sheet1!$B$2:$D$6, "")

becomes

IF("apple"={"apple";"orange";"apple";"apple";"grapes"}, {10; 11; 8, 9; 9; 10, 11; 11; 12, 14; 10; 10, 15; 15; 14}, "")

becomes

IF({TRUE;FALSE;TRUE;TRUE;FALSE}, {10; 11; 8, 9; 9; 10, 11; 11; 12, 14; 10; 10, 15; 15; 14}, "")

becomes

{10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}

*Step 2 - Return the k-th smallest value*

=SMALL({10; 11; 8, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""}, COLUMN(A1))

becomes

=SMALL({10; 11; **8**, ""; ""; "", 11; 11; 12, 14; 10; 10, ""; ""; ""},1)

and returns 8.

### Download excel sample file

merge matching rows.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test,[value_if_true], [value_if_false]**) **Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k)

Returns the k-th smallest number in this data set.

**COLUMN(**reference**)** Returns the column number of a reference

**shomyx asks:**

apple: 8 10 10 10 11 11 11 12 14

keep the original order from sheet 1 like this:

apple: 10 11 8 11 11 12 14 10 10

how can I change the formula(s) to do it?

Thanks!!

**Answer**

Function MergeMatchingRows(SearchValue As Range, SearchRange As Range) Dim r, c, ic As Single Dim temp() As Variant ReDim temp(0) For r = 1 To SearchRange.Rows.Count If SearchRange.Cells(r, 1) = SearchValue Then For c = 2 To SearchRange.Columns.Count If SearchRange.Cells(r, c) <> "" Then temp(UBound(temp)) = SearchRange.Cells(r, c) ReDim Preserve temp(UBound(temp) + 1) End If Next c End If Next r ReDim Preserve temp(UBound(temp) - 1) ic = Range(Application.Caller.Address).Columns.Count For c = UBound(temp) To ic ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next c MergeMatchingRows = temp End Function

**Download excel file *.xlsm**

merge-matching-rows2.xlsm

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Merge three columns into one list in excel

Question: How do I merge three columns into one list? Answer: Excel 2007 array formula in D2: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Merge tables based on a condition

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]

Automate excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Group a number of rows together by the first column

Mike asks: Oscar, I'm hoping you can help. I am trying to group a number of rows together by the […]

Combine cell ranges eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Tracking a stock portfolio #2 in excel

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

### 3 Responses to “Merge matching rows in excel”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

This merging partially works for me, but instead of ordering the values on sheet 2 from smallest to largest, I need to keep the original order for sheet 1, for example instead of:

apple: 8 10 10 10 11 11 11 12 14

keep the original order from sheet 1 like this:

apple: 10 11 8 11 11 12 14 10 10

how can I change the formula(s) to do it?

Thanks!!

shomyx,

Great question! I can´t do it with array formulas, really complicated.

Instead I created a user defined function. See new content above!

Please help me with this problem. I am trying to make a sheet of premier leauge points and try to get the result of winners for each week. And I came across with a problem that two people have equal points and excell won't work for two values. Can anybody help me? Thank you for your kindness.