Finding same value in different rows in two columns

Hi all,

Does anyone know how to find same value in different rows in two columns?

Here if the value in "Ref1" column in first row matches the value in "Ref2" column in second row, I will add "match" to the "Comments" column for both two rows.

The same value cannot appear in the same row.

Original:

Ref1	Ref2	Comments
1234	5678
0123	1234
ABC	EFGH
XYZW	ABCDEF
EFGH	ABCD

Desire result:

Ref1	Ref2	Comments
1234	5678	match
0123	1234	match
ABC	EFGH	match
XYZW	ABCDEF
EFGH	ABCD	match

Workflow

Developer

Transformation

Help

Preparation

Accepted answers

BS_THE_ANALYST

@JeremyZ here's one way (it's a bit scrappy, I've not got much time). I'll refine it shortly

All the best,
BS

ContainsColumn.yxmd

martinding

@JeremyZ

I see what you mean.

Same value example.yxmd

Christina_H

Here's another option

Match values.yxmd

All comments

BS_THE_ANALYST

@JeremyZ here's one way (it's a bit scrappy, I've not got much time). I'll refine it shortly

All the best,
BS

ContainsColumn.yxmd

NMangera

Hi @JeremyZ

JeremyZ

@BS_THE_ANALYSTThanks for your help!

But when I try to run my raw data which contains more than 300 rows, the "Append Fields" tool shows error message "There were more than 16 records in the source", how could I fix it? thanks

martinding

Hi @JeremyZ,

Here's another approach:

Same value example.yxmd

JeremyZ

@martinding Thanks for your help!

But the last two rows need to show "match" in "Comment" column as well.

JitendraPal

Hi @JeremyZ you can post this as a weekly challenge, you would have received more responses/solutions to the same problem, anyways I'll also post my approach once I work through it.

on the other hand, you can still submit this as an weekly challenge.

Christina_H

Here's another option

Match values.yxmd

aisha002

To find the same value in different rows in two columns, you can use a combination of Excel functions. One way to do this is by using the "IF" and "COUNTIF" functions. First, create a new column next to the two columns you want to compare. In the first cell of the new column, use the formula: "=IF(COUNTIF(A:A,B1)>0,"Match","No match")". This formula compares the value in cell B1 to the values in column A and returns "Match" if it finds a match, or "No match" if it doesn't. You can then drag the formula down the entire column to compare all the values in column B to the values in column A. This method can help you quickly identify any matching values between the two columns.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703