Click here for important information about

Multiple Cell text comparison

Microsoft Excel 2007
December 31, 2009 at 03:39:00
Specs: Windows Vista Ultimate
I would like to check that the contents of a single cell contain all of the contents from a number of other cells. Here is an example.

Sheet 1 A1 = 111]222]333
Sheet 2 A5 = r4]r5
Sheet 3 A1 = 111]222]333]r4]r5

in Sheet 4 cell A1 the formula is basically going to say if sheet3!A1 contains the contents of Sheet1!A1 and the contents of Sheet2!A5 then "pass" else "FAIL"

This formula will be copied from column to column in sheet four so that the relative column reference keeps moving. So Sheet4 B1 says if Sheet3!B1 = Sheet1!B1 and Sheet2!B5 and so on and so forth.

Thank you for looking at this issue

See More: Multiple Cell text comparison

December 31, 2009 at 05:32:23

Put this formula in A1, then drag as required:

Note that the formula has been split onto three lines for ease of viewing. Reassemble into one line for use as a formula.
If the cell in sheet 3 is empty but there is something in the relevant cell in sheet 1 and/or sheet 2 this formula returns "Empty" as a warning. (You can change this to Fail if preferred).
If the cells in sheets 1, 2 and 3 are all empty the formula returns nothing "".


Report •

February 9, 2010 at 07:54:20
Sorry for the delay in responding but I got moved away from this project for a while.

When I use the code. I started out with a blank sheet. In that sheet I placed this much of the code in A1. The idea is that the position on sheet 1 matches the position on sheet 2, Sheet two is the original data, sheet three and four contain the converted data. So data in sheet 2 a1 will be in sheet 3 a40. I want to see pass or fail on sheet 1 a1.


and I receive an error stating that one of the data types is invalid. Both cells are formatted as text. any idea why there is a data type error?

sheet 2 A1 = U1882296
sheet 3 A40 = e1234, f445566, U1882296

right click, format cells, both show text as content type.

Report •

February 9, 2010 at 08:35:09

When do you get this error.

Is it when you enter the formula or when you enter data on Sheet 2 or Sheet3.

Formulas return errors such as #VALUE or #NA, so I am not sure when this 'one of the data types is invalid' appears.

The formula and data you posted actually worked when I entered the formula on Sheet1 and the data in A1 and A40 on sheets 2 & 3.

This formula needs to be:
as FIND returns #VALUE if no match is found.

So try that instead.


Report •

Related Solutions

February 9, 2010 at 08:39:37
Thank you very much, all I needed was the ISERROR. I thought that the zero would be returned if find failed.

Thanks again, I really appreciate your help.


Report •

Ask Question