## Merge matching rows in excel (text values)

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

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

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

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

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