## Merge matching rows in excel

*Article updated on May 11, 2012*

**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

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

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.