## Merge matching rows

**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 ..... 14In 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:**

Array formula in cell A2:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

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*

*Step 1 - Filter values in matching rows*

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

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

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

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

=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 file

**shomyx asks:**

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

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

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

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 Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

Merge three columns into one list

The above image demonstrates a formula that adds values in three different columns into one column. Formula in H2: =IFERROR(INDEX($B$3:$B$7, […]

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 […]

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 ignore blank cells

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

### 3 Responses to “Merge matching rows”

### Leave a Reply to shomyx

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

Paste image link to your comment.

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