ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   compare & match text (https://www.excelbanter.com/excel-worksheet-functions/143647-compare-match-text.html)

Susanne

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

vezerid

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


Susanne

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



vezerid

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


Susanne

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



vezerid

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



All times are GMT +1. The time now is 05:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com