Solved

Need to compare the string of two columns

Posted on 2013-01-23
9
422 Views
Last Modified: 2013-01-23
Need to compare the string of two column and need the result as true/False.

Example:

A1 - suski,white A2 - Ward,William A3 - Wilson, Tony A4 - Denise ,Mary A5 - Jouswa, Stephen

B1 - Jouswa, Stephen B2 - Wilson,Tony B3 - Denise ,Mary B4 - Briggs, Matt B5 - Suski,White

Take the String in A1 and need to compare with all the string in the B column, if it present in B column .Then print True on C1.
0
Comment
Question by:Venugopal N
  • 4
  • 4
9 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 175 total points
ID: 38808898
Hello

In column C, starting in C2, assuming row 1 has labels:

=IF(ISERROR(MATCH(A2,B:B,0)),"","True")

or

=IF(COUNTIF(B:B,A2),"True","")

copy down

cheers, teylyn
0
 
LVL 7

Assisted Solution

by:Jignesh Thar
Jignesh Thar earned 75 total points
ID: 38808899
Put below in C1 and copy it down
=IF(ISERROR(VLOOKUP(A1,B:B,1,FALSE)),"","True")
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38808902
jigneshthar, Vlookup will work, too, but will be slower than Match(). Countif() is probably the fastest function for this. Does not make much of a difference in small files, but for large datasets it might pay off to use a fast function.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38808921
teylyn - I wan't aware about this. Do you know why that is? Doesn't vlookup end as soon it finds first exact match which is similar to Match loop?

Countif should be longest as it has to loop through entire column B to get the result.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38808925
Vlookup is slower because after finding the item it then needs to locate the column to return. This is another operation that cost a few nanoseconds. Match simply returns the row index.

Countif works in a completely different way and is super-fast.
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38808967
teylyn - Very interesting!! Can you point me to some resources where it comparaes various functions from performance point of view?

If Vlookup is to return same column (B:B), it doesnt necessarily have to do another lookup - assuming it is smart enough. :-)
0
 
LVL 11

Author Closing Comment

by:Venugopal N
ID: 38808995
Thanks for the reply.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38809010
@Venurajav, sorry for the highjack. Maybe you get something out of it, too.


@jigneshthar,
Some light reading. If you are interested in fast Excel, then read anything by Charles Williams.

http://fastexcel.wordpress.com/2011/07/20/developing-faster-lookups-part-1-using-excels-functions-efficiently/

Re Countif:
" If you cannot use two cells, use COUNTIF. It is generally faster than an exact match lookup:" source: http://msdn.microsoft.com/en-us/library/office/aa730921(v=office.12).aspx (by Charles Williams)

And a discussion among Excel MVPs about what is faster: Vlookup or Index/Match (mind you, it turns out that Vlookup is faster, but they compare it with the Index/Match COMBO, where Match is nested in an Index function, and not with a simple Match on its own.

Also, there's code to time things, so if you want to give this a go ...

http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP

cheers, teylyn
0
 
LVL 7

Expert Comment

by:Jignesh Thar
ID: 38809019
@Venurajav, sorry about barrage of posts for simple solution!! :-) Am still learning and I see there is long way to go...

@teylyn, thanks for all the resources. Will certainly going to digest so that I can write performant formula / code. Thanks again!! :-)
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel - Data Validation 3 30
Excel VBA - triggering a Sheet event on another workbook 3 29
Copying from excel I am getting extra text 11 44
VBA taking too long 5 20
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question