## Merge matching rows in excel (text values)

*Article updated on June 18, 2011*

I'm using Excel 2007 and I have 2 Sheets:

Sheet1:

____A B C D E F

bla X X X

ble X X

Sheet2:

Name Letter1 Letter2

bla___A______B

ble___A______C

bla___B______A

I'm trying to make a formula to put in the cell where is the first X after bla, that checks the Sheet 2 for name and populates with the X the letters it finds.

### Answer:

Your question is similar to the question in this post: Merge matching rows in excel. But this time it is about unique text values and not numbers (all).

**Sheet2**

**Sheet1**

**Array formula in Sheet1, cell B2:**

**How to create an array formula**

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

**Named ranges**

tbl_text: Sheet2!A1:C3

**How to create a named range**

- Select cell range Sheet2!A1:C3
- Type
*tbl_text*in name box - Press Enter

**How to copy array formula**

- Select cell B2
- Copy (Ctrl + c)
- Select cell range B2:D3
- Paste (Ctrl + v)

**Download excel sample file for this article.**

Rui-Costa.xls

(Excel 2007 Workbook *.xlsx)

**Functions in this article**

**ROW(**reference**)** Returns the rownumber of a reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

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

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]### 2 Responses to “Merge matching rows in excel (text values)”

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

Thanks a lot Oscar for your time and help! :)

Please help!

I have a worksheet like this:

A B C D E

1 5:30 AM 6:00 PM 530 1800

2 Saturday s

3 Sunday x

4 Holiday hd

5

On column C and D, I have there data inputs that will be converted to time values on columns A and B, my problem is I want A2:B2 be merged upon inputting "s" which is Saturday, "x" as Sunday and "hd" as holiday so that it will appear centered, I made this as a daily time record printable format. thank you for the help..