# Help with formula comparing two sets of data

#### jahli

##### Board Regular
I am so excited to find this forum because I need some formula assistance.

I have two worksheets containing data that I need to compare to validate that an entry on worksheet CON has a corresponding entry on worksheet SAW. I would use Vlookup, except the data I need to compare in SAW is included in a long string of text.

The formula or combination of proceedures and formula needs to be able to find c00709364 in the text string "RIP Manager (5K) (ID: emr_na-c00723764-1) " and return a TRUE (or FALSE if not found.)

Here is a couple of entry examples from the two worksheets:
Compare
CON data to SAW data
c00709364 RIP Manager (26K) (ID: emr_na-c00726155-1)
c00710005 Managing Presses (7K) (ID: emr_na-c00726192-1)
c00711679 Installing Remote Manager (2K) (ID: emr_na-c00711679-1)
c00726192 RIP Manager (5K) (ID: emr_na-c00723764-1)

I hope this is understandable.

jahli

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Try:

=OR(ISNUMBER(SEARCH(A1,SAW!\$A\$1:\$A\$10)))

Where A1 is the string to search for in your CON sheet

SAW!A1:A10 contain the lookup table

adjust ranges to suit and confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You should see {} brackets appear around the formula if entered correctly

WELCOME TO THE BOARD!

Hmm... assuming that the value you are validating is in cell A1, and the list you are validating against is in Saw!A1:A100, then you could do

Code:
``=or(not(iserror(search(a1,saw!a1:a100))))``
confirmed with Ctrl+Shift_enter, rather than just enter. You CANNOT apply this to an entore column of values, you must retsrict your data column to a discreet number of entries... the fewer, the more efficient this will perform.

=OR(ISNUMBER(SEARCH(A1,SAW!\$A\$1:\$A\$10)))

One of these days I will remember the Isnumber() function in this application and eliminate the fourth function call :wink:

Thank you so much for your suggestion and the revision. Unfortunately it is not working for me. Were you able to make it work with the 4 lines of data I included in my example?

Yup:
Book1
BCDE
1TRUEemr_na-c00723764-1
2FALSEemr_na-GHJ00726155-1
3
4
5c00709364RIPManager(26K)(ID:emr_na-c00726155-1)
6c00710005ManagingPresses(7K)(ID:emr_na-c00726192-1)
7c00711679InstallingRemoteManager(2K)(ID:emr_na-c00711679-1)
8c00726192RIPManager(5K)(ID:emr_na-c00723764-1)
Sheet1

Did you press Ctrl+Shift+Enter when you completed entering the formula?

Ah.....I finally got it right. Woohooooo!!!! Thank you!

There are over 800 lines in the data I am comparing. Is there some way, other than entering a separate formula on each line, to apply this formula so that it searches the entire A column in the SAW sheet 1 through 800?

When copied down the page the formula changes to:
=OR(ISNUMBER(SEARCH(A3,SAW!A3:A6))) when it needs to be
=OR(ISNUMBER(SEARCH(A3,SAW!A2:A5)))

I tried to do a find (SAW1A*) and replace (SAW1A2:A5), but got a formula error right off the bat. Any suggestions?

Code:
``=OR(ISNUMBER(SEARCH(A3,SAW!\$A\$2:\$A\$5)))``

Thank you again!!!!!!!!! You're a star!!!

Replies
2
Views
624
Replies
2
Views
311
Replies
3
Views
829
Replies
4
Views
733
Replies
4
Views
457

1,217,764
Messages
6,138,471
Members
450,140
Latest member
myexcel202424

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back