Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 22nd 07, 12:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 20
Default compare & match text

In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.

Could anyone please help me.

Thanks,
Susanne

  #2   Report Post  
Old May 22nd 07, 12:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default compare & match text

On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.

Could anyone please help me.

Thanks,
Susanne


In column C (C2)
=IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match")

You can then use filter on C or simply spot inconsistencies.

HTH
Kostis Vezerides

  #3   Report Post  
Old May 22nd 07, 01:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 20
Default compare & match text

I have changed the formula according to my worksheet but it only gives me
N/A...

I have my names/room mates in the columns of B and Q, and my Excel uses the
; instead of commas to separate in formulas. Could you please look through it
for me? Have I done wrong ...?

My first row is #3.

=IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")

Rgs
Susanne


"vezerid" wrote:

On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.

Could anyone please help me.

Thanks,
Susanne


In column C (C2)
=IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match")

You can then use filter on C or simply spot inconsistencies.

HTH
Kostis Vezerides


  #4   Report Post  
Old May 22nd 07, 03:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default compare & match text

On May 22, 3:26 pm, Susanne wrote:
I have changed the formula according to my worksheet but it only gives me
N/A...

I have my names/room mates in the columns of B and Q, and my Excel uses the
; instead of commas to separate in formulas. Could you please look through it
for me? Have I done wrong ...?

My first row is #3.

=IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")

Rgs
Susanne

"vezerid" wrote:
On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.


Could anyone please help me.


Thanks,
Susanne


In column C (C2)
=IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match")


You can then use filter on C or simply spot inconsistencies.


HTH
Kostis Vezerides


Hmmm,

I know I had a small mistake in my formula, but I am not sure it
should cause #N/A. Anyway, try the following, small modification of
your formula:

=IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")

Let us know if this one works.

Kostis

  #5   Report Post  
Old May 22nd 07, 03:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 20
Default compare & match text

Sorry, still gets the #N/A..

Susanne


"vezerid" wrote:

On May 22, 3:26 pm, Susanne wrote:
I have changed the formula according to my worksheet but it only gives me
N/A...

I have my names/room mates in the columns of B and Q, and my Excel uses the
; instead of commas to separate in formulas. Could you please look through it
for me? Have I done wrong ...?

My first row is #3.

=IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")

Rgs
Susanne

"vezerid" wrote:
On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.


Could anyone please help me.


Thanks,
Susanne


In column C (C2)
=IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match")


You can then use filter on C or simply spot inconsistencies.


HTH
Kostis Vezerides


Hmmm,

I know I had a small mistake in my formula, but I am not sure it
should cause #N/A. Anyway, try the following, small modification of
your formula:

=IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")

Let us know if this one works.

Kostis




  #6   Report Post  
Old May 22nd 07, 05:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 751
Default compare & match text

On May 22, 5:35 pm, Susanne wrote:
Sorry, still gets the #N/A..

Susanne

"vezerid" wrote:
On May 22, 3:26 pm, Susanne wrote:
I have changed the formula according to my worksheet but it only gives me
N/A...


I have my names/room mates in the columns of B and Q, and my Excel uses the
; instead of commas to separate in formulas. Could you please look through it
for me? Have I done wrong ...?


My first row is #3.


=IF(INDEX($B$3:$B$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")


Rgs
Susanne


"vezerid" wrote:
On May 22, 2:29 pm, Susanne wrote:
In column A I have the names of the participants and in column B I have the
names of their room mate. Meaning that everybody are mentioned in both
columns of course. Now I need to check that if John is Kenns room mate then
Kenn is also Johns room mate.


Could anyone please help me.


Thanks,
Susanne


In column C (C2)
=IF(INDEX($A$2:$A$100,MATCH(B2,$A$2:$A$100,0))=B2, "Match","No match")


You can then use filter on C or simply spot inconsistencies.


HTH
Kostis Vezerides


Hmmm,


I know I had a small mistake in my formula, but I am not sure it
should cause #N/A. Anyway, try the following, small modification of
your formula:


=IF(INDEX($Q$3:$Q$100;MATCH(Q3;$B$3:$B$100;0))=Q3; "Match";"No match")


Let us know if this one works.


Kostis


Try something simpler (I should have thought of it to start with). In
C2:

=VLOOKUP(B2,A:B,2,0)

This should find the match of the pair. It should be the same name as
in A2. See what you get with this formula.

Kostis



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Spreadsheet Assistance (Match, Compare) Nancy Excel Worksheet Functions 1 January 17th 07 03:05 AM
Lookup? Match? pulling rows from one spreadsheet to match a text f cjax Excel Worksheet Functions 3 July 21st 06 02:51 PM
Compare and Match Functions DP63 Excel Discussion (Misc queries) 1 June 20th 06 04:00 PM
Compare and match names and extract a cell content dexsourcesys Excel Worksheet Functions 1 January 19th 06 08:51 PM
Want to compare two columns and then fill with data from the match Needy C Excel Worksheet Functions 1 January 11th 06 05:52 AM


All times are GMT +1. The time now is 11:28 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017