Help with formula comparing two sets of data

jahli

Board Regular
Joined
Oct 2, 2006
Messages
121
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
=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

Board Regular
Joined
Oct 2, 2006
Messages
121

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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?
 

jahli

Board Regular
Joined
Oct 2, 2006
Messages
121

ADVERTISEMENT

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

jahli

Board Regular
Joined
Oct 2, 2006
Messages
121
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?
 

Forum statistics

Threads
1,136,595
Messages
5,676,707
Members
419,644
Latest member
KeelsM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top