Help with formula comparing two sets of data

[jahli]

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.

Thanks for your help.
jahli

 

[NBVC]

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

 

[hatman]

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

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

 

[hatman]

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

 

[jahli]

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?

 

[hatman]

Yup:

Book1
	B	C	D	E
1	TRUE	emr_na-c00723764-1
2	FALSE	emr_na-GHJ00726155-1
3
4
5	c00709364RIPManager(26K)(ID:emr_na-c00726155-1)
6	c00710005ManagingPresses(7K)(ID:emr_na-c00726192-1)
7	c00711679InstallingRemoteManager(2K)(ID:emr_na-c00711679-1)
8	c00726192RIPManager(5K)(ID:emr_na-c00723764-1)
Sheet1

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

 

[jahli]

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

 

[jahli]

One additional question:

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?

 

[hatman]

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

 

[jahli]

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